Group counting similar to the group counting function of groupby

Previously, my colleague sent a statement to implement a simple function, similar to the group count function of groupby. Because the where condition has the like condition, it cannot be implemented using groupby. SELECTa. n0, B. n1, c. n2, d. n3, e. n4, f. n5, g. n6, h. n7, I. n8, j. n9from (selectcount (*) N0fromtbl_loginfo_20141110wherekeyrecord

Previously, my colleague sent a statement to implement a simple function, similar to the group by grouping and counting function, because the where condition has the like condition and cannot be implemented by group. SELECT. n0, B. n1, c. n2, d. n3, e. n4, f. n5, g. n6, h. n7, I. n8, j. distinct from (select count (*) N0 from tbl_loginfo_20141110 where keyrecord

Previously, my colleague sent a statement to implement a simple function, similar to the group by grouping and counting function, because the where condition has the like condition and cannot be implemented by group. SELECT. n0, B. n1, c. n2, d. n3, e. n4, f. n5, g. n6, h. n7, I. n8, j. distinct 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_logi Nfo_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 (*) explain from tbl_loginfo_20141110 where keyrecord like '9% 'or keyrecord like 'gj _ 9%') j. To understand the statement performance, I did the following similar tests: 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 '; -- 357 Malter system flush shared_pool; alter system flush buffer_cache; SELECT. n0, B. n1, c. n2, d. n3, e. n4, f. n5, g. n6, h. n7, I. n8, j. distinct 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 (*) distinct from a where object_name like 'J %' or ob Ject_name like 'v % ') j; -- 58 salter system flush shared_pool; alter system flush buffer_cache; -- after rewriting, 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) N9from a; -- 19 s -- Compare execution plan: -- The former execution plan: SQL> explain plan for 2 SELECT. n0, B. n1, c. n2, d. n3, e. n4, f. n5, g. n6, h. n7, I. n8, j. distinct 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 (*) returned 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 fo R plan type: PLAN_TABLE_OUTPUT partition Plan hash value: 2527411742 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | | 1 | 130 | 123 K (1) | 00:24:46 | 1 | nested loops | 1 | 130 | 123 K (1) | 00:24:46 | 2 | nested loops | 1 | 117 | 111 K (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 | * 2 4 | 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 | 123 79 (1) | 00:02:29 | identified Predicate Information (identified by operation id): Pushed 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 ("OBJEC T_NAME "LIKE 'a % 'OR" OBJECT_NAME "LIKE 'v %') -- the latter executes the plan: 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 li Ke '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) limit 13 from a; Explained. elapsed: 00:00:00. 01SQL> @ getplan 'General, outline, starts 'enter value for plan type: PLAN_TABLE_OUTPUT partition Plan hash value: 3918351354 ------------ ----------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 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) can be seen that the former has 10 full table scans, and the latter has 1 full table scan. This greatly reduces the time. Reduced from 58s to 19 s. Optimization of this SQL statement mainly involves the conversion of ideas. The difficulty lies in how to convert 10 full table scans into one full table scan. In OLAP, you can add and execute SQL statements at a faster speed.