之前同事发过一个语句，实现的功能比较简单，类似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
之前同事发过一个语句，实现的功能比较简单，类似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 - productiondrop table a;create table a as select * from dba_objects where rownum 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.15sql> @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.01sql> @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速度更快。