之前同事发过一个语句，实现的功能比较简单，类似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 A