类似groupby的分组计数功能

之前同事发过一个语句，实现的功能比较简单，类似group by的分组计数功能，因为where条件有like，又无法用group by来实现。
SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from 
 (select count(*) N0 from tbl_loginfo_20141110 where keyrecord like '0%' or keyrecord like 'GJ_0%') a,
 (select count(*) N1 from tbl_loginfo_20141110 where keyrecord like '1%' or keyrecord like 'GJ_1%') b,
 (select count(*) N2 from tbl_loginfo_20141110 where keyrecord like '2%' or keyrecord like 'GJ_2%') c,
 (select count(*) N3 from tbl_loginfo_20141110 where keyrecord like '3%' or keyrecord like 'GJ_3%') d,
 (select count(*) N4 from tbl_loginfo_20141110 where keyrecord like '4%' or keyrecord like 'GJ_4%') e,
 (select count(*) N5 from tbl_loginfo_20141110 where keyrecord like '5%' or keyrecord like 'GJ_5%') f,
 (select count(*) N6 from tbl_loginfo_20141110 where keyrecord like '6%' or keyrecord like 'GJ_6%') g,
 (select count(*) N7 from tbl_loginfo_20141110 where keyrecord like '7%' or keyrecord like 'GJ_7%') h,
 (select count(*) N8 from tbl_loginfo_20141110 where keyrecord like '8%' or keyrecord like 'GJ_8%') i,
 (select count(*) N9 from tbl_loginfo_20141110 where keyrecord like '9%' or keyrecord like 'GJ_9%') j;
 
为了了解语句的性能，我做了如下类似的测试： 
select * from v$version; --Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
drop table a;
create table a as select * from dba_objects where rownum<=50000;
begin
 for x in 1..6 loop
 insert into a select * from a;
 end loop;
 commit;
end;

select count(*) from a; --3200000
select bytes/1024/1024 from user_segments where segment_name='A'; --357M

alter system flush shared_pool;
alter system flush buffer_cache;

SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from 
 (select count(*) N0 from a where object_name like 'A%' or object_name like 'V%') a,
 (select count(*) N1 from a where object_name like 'B%' or object_name like 'V%') b,
 (select count(*) N2 from a where object_name like 'C%' or object_name like 'V%') c,
 (select count(*) N3 from a where object_name like 'D%' or object_name like 'V%') d,
 (select count(*) N4 from a where object_name like 'E%' or object_name like 'V%') e,
 (select count(*) N5 from a where object_name like 'F%' or object_name like 'V%') f,
 (select count(*) N6 from a where object_name like 'G%' or object_name like 'V%') g,
 (select count(*) N7 from a where object_name like 'H%' or object_name like 'V%') h,
 (select count(*) N8 from a where object_name like 'I%' or object_name like 'V%') i,
 (select count(*) N9 from a where object_name like 'J%' or object_name like 'V%') j;
--58s
alter system flush shared_pool;
alter system flush buffer_cache;

--改写后
select 
sum(case when object_name like 'A%' or object_name like 'V%' then 1 else 0 end) N0,
sum(case when object_name like 'B%' or object_name like 'V%' then 1 else 0 end) N1, 
sum(case when object_name like 'C%' or object_name like 'V%' then 1 else 0 end) N2, 
sum(case when object_name like 'D%' or object_name like 'V%' then 1 else 0 end) N3, 
sum(case when object_name like 'E%' or object_name like 'V%' then 1 else 0 end) N4, 
sum(case when object_name like 'F%' or object_name like 'V%' then 1 else 0 end) N5, 
sum(case when object_name like 'G%' or object_name like 'V%' then 1 else 0 end) N6, 
sum(case when object_name like 'H%' or object_name like 'V%' then 1 else 0 end) N7, 
sum(case when object_name like 'I%' or object_name like 'V%' then 1 else 0 end) N8, 
sum(case when object_name like 'J%' or object_name like 'V%' then 1 else 0 end) N9
from a; 

--19s





--对比执行计划：
--前者执行计划：
SQL> explain plan for
 2 SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from
 3 (select count(*) N0 from a where object_name like 'A%' or object_name like 'V%') a,
 4 (select count(*) N1 from a where object_name like 'B%' or object_name like 'V%') b,
 5 (select count(*) N2 from a where object_name like 'C%' or object_name like 'V%') c,
 6 (select count(*) N3 from a where object_name like 'D%' or object_name like 'V%') d,
 7 (select count(*) N4 from a where object_name like 'E%' or object_name like 'V%') e,
 8 (select count(*) N5 from a where object_name like 'F%' or object_name like 'V%') f,
 9 (select count(*) N6 from a where object_name like 'G%' or object_name like 'V%') g,
 10 (select count(*) N7 from a where object_name like 'H%' or object_name like 'V%') h,
 11 (select count(*) N8 from a where object_name like 'I%' or object_name like 'V%') i,
 12 (select count(*) N9 from a where object_name like 'J%' or object_name like 'V%') j;

Explained.

Elapsed: 00:00:00.15
SQL> @getplan
'general,outline,starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2527411742

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 123K (1)| 00:24:46 |
| 1 | NESTED LOOPS | | 1 | 130 | 123K (1)| 00:24:46 |
| 2 | NESTED LOOPS | | 1 | 117 | 111K (1)| 00:22:17 |
| 3 | NESTED LOOPS | | 1 | 104 | 99032 (1)| 00:19:49 |
| 4 | NESTED LOOPS | | 1 | 91 | 86653 (1)| 00:17:20 |
| 5 | NESTED LOOPS | | 1 | 78 | 74274 (1)| 00:14:52 |
| 6 | NESTED LOOPS | | 1 | 65 | 61895 (1)| 00:12:23 |
| 7 | NESTED LOOPS | | 1 | 52 | 49516 (1)| 00:09:55 |
| 8 | NESTED LOOPS | | 1 | 39 | 37137 (1)| 00:07:26 |
| 9 | NESTED LOOPS | | 1 | 26 | 24758 (1)| 00:04:58 |
| 10 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 11 | SORT AGGREGATE | | 1 | 66 | | |
|* 12 | TABLE ACCESS FULL| A | 91587 | 5903K| 12379 (1)| 00:02:29 |
| 13 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 14 | SORT AGGREGATE | | 1 | 66 | | |
|* 15 | TABLE ACCESS FULL| A | 137K| 8831K| 12379 (1)| 00:02:29 |
| 16 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 17 | SORT AGGREGATE | | 1 | 66 | | |
|* 18 | TABLE ACCESS FULL | A | 85818 | 5531K| 12379 (1)| 00:02:29 |
| 19 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 20 | SORT AGGREGATE | | 1 | 66 | | |
|* 21 | TABLE ACCESS FULL | A | 111K| 7158K| 12379 (1)| 00:02:29 |
| 22 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 23 | SORT AGGREGATE | | 1 | 66 | | |
|* 24 | TABLE ACCESS FULL | A | 86539 | 5577K| 12379 (1)| 00:02:29 |
| 25 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 26 | SORT AGGREGATE | | 1 | 66 | | |
|* 27 | TABLE ACCESS FULL | A | 91587 | 5903K| 12379 (1)| 00:02:29 |
| 28 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 29 | SORT AGGREGATE | | 1 | 66 | | |
|* 30 | TABLE ACCESS FULL | A | 228K| 14M| 12379 (1)| 00:02:29 |
| 31 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 32 | SORT AGGREGATE | | 1 | 66 | | |
|* 33 | TABLE ACCESS FULL | A | 87981 | 5670K| 12379 (1)| 00:02:29 |
| 34 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 35 | SORT AGGREGATE | | 1 | 66 | | |
|* 36 | TABLE ACCESS FULL | A | 84376 | 5438K| 12379 (1)| 00:02:29 |
| 37 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 38 | SORT AGGREGATE | | 1 | 66 | | |
|* 39 | TABLE ACCESS FULL | A | 112K| 7251K| 12379 (1)| 00:02:29 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 12 - filter("OBJECT_NAME" LIKE 'J%' OR "OBJECT_NAME" LIKE 'V%')
 15 - filter("OBJECT_NAME" LIKE 'I%' OR "OBJECT_NAME" LIKE 'V%')
 18 - filter("OBJECT_NAME" LIKE 'H%' OR "OBJECT_NAME" LIKE 'V%')
 21 - filter("OBJECT_NAME" LIKE 'G%' OR "OBJECT_NAME" LIKE 'V%')
 24 - filter("OBJECT_NAME" LIKE 'F%' OR "OBJECT_NAME" LIKE 'V%')
 27 - filter("OBJECT_NAME" LIKE 'E%' OR "OBJECT_NAME" LIKE 'V%')
 30 - filter("OBJECT_NAME" LIKE 'D%' OR "OBJECT_NAME" LIKE 'V%')
 33 - filter("OBJECT_NAME" LIKE 'C%' OR "OBJECT_NAME" LIKE 'V%')
 36 - filter("OBJECT_NAME" LIKE 'B%' OR "OBJECT_NAME" LIKE 'V%')
 39 - filter("OBJECT_NAME" LIKE 'A%' OR "OBJECT_NAME" LIKE 'V%')
 
 
 
 
 
--后者执行计划： 
SQL> explain plan for
 2 select
 3 sum(case when object_name like 'A%' or object_name like 'V%' then 1 else 0 end) N0,
 4 sum(case when object_name like 'B%' or object_name like 'V%' then 1 else 0 end) N1,
 5 sum(case when object_name like 'C%' or object_name like 'V%' then 1 else 0 end) N2,
 6 sum(case when object_name like 'D%' or object_name like 'V%' then 1 else 0 end) N3,
 7 sum(case when object_name like 'E%' or object_name like 'V%' then 1 else 0 end) N4,
 8 sum(case when object_name like 'F%' or object_name like 'V%' then 1 else 0 end) N5,
 9 sum(case when object_name like 'G%' or object_name like 'V%' then 1 else 0 end) N6,
 10 sum(case when object_name like 'H%' or object_name like 'V%' then 1 else 0 end) N7,
 11 sum(case when object_name like 'I%' or object_name like 'V%' then 1 else 0 end) N8,
 12 sum(case when object_name like 'J%' or object_name like 'V%' then 1 else 0 end) N9
 13 from a;

Explained.

Elapsed: 00:00:00.01
SQL> @getplan
'general,outline,starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3918351354

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 12349 (1)| 00:02:29 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | TABLE ACCESS FULL| A | 3097K| 194M| 12349 (1)| 00:02:29 |
---------------------------------------------------------------------------

Note
-----
 - dynamic sampling used for this statement (level=2) 


可以看出，前者10次全表扫描，后者1次全表扫描。从而时间上也大大降低了。由58s降低到19s。
优化这个sql主要还是思路的转换，难点在于怎样把10次全表扫描转化成1次全表扫描。
在OLAP中，可以加并行使sql速度更快。