人工group by cube的思路

〇〇

g5 19 7 7

g6 39 15 14

〇〇

真奇怪，谁能解释？

〇〇

explain plan for
create table tcube as select g1,g2,g3,sum(v1)s1,count(v1)c1,grouping_id(g1,g2,g3)gid from t group by cube(g1,g2,g3);
select * from table(dbms_xplan.display);

explain plan for
create table tcube2 as select G1,G2,G3,count(v1)c1,sum(v1)s1,0 gid from t group by G1,G2,G3 union all
select null G1,G2,G3,count(v1)c1,sum(v1)s1,1 gid from t group by null,G2,G3               union all
select G1,null G2,G3,count(v1)c1,sum(v1)s1,2 gid from t group by G1,null,G3               union all
select null G1,null G2,G3,count(v1)c1,sum(v1)s1,3 gid from t group by null,null,G3        union all
select G1,G2,null G3,count(v1)c1,sum(v1)s1,4 gid from t group by G1,G2,null               union all
select null G1,G2,null G3,count(v1)c1,sum(v1)s1,5 gid from t group by null,G2,null        union all
select G1,null G2,null G3,count(v1)c1,sum(v1)s1,6 gid from t group by G1,null,null        union all
select null G1,null G2,null G3,count(v1)c1,sum(v1)s1,7 gid from t group by null,null,null ;
select * from table(dbms_xplan.display);

explain plan for
create table tcube3 as
with s as
(select G1,G2,G3,count(v1)c1,sum(v1)s1,0 gid from t group by G1,G2,G3)
select * from s union all
select null G1,G2,G3,sum(c1)c1,sum(s1)s1,1 gid from s group by null,G2,G3               union all
select G1,null G2,G3,sum(c1)c1,sum(s1)s1,2 gid from s group by G1,null,G3               union all
select null G1,null G2,G3,sum(c1)c1,sum(s1)s1,3 gid from s group by null,null,G3        union all
select G1,G2,null G3,sum(c1)c1,sum(s1)s1,4 gid from s group by G1,G2,null               union all
select null G1,G2,null G3,sum(c1)c1,sum(s1)s1,5 gid from s group by null,G2,null        union all
select G1,null G2,null G3,sum(c1)c1,sum(s1)s1,6 gid from s group by G1,null,null        union all
select null G1,null G2,null G3,sum(c1)c1,sum(s1)s1,7 gid from s group by null,null,null ;
select * from table(dbms_xplan.display);

SQL> set lines 132 pages 50000
SQL> explain plan for
  2  create table tcube as select g1,g2,g3,sum(v1)s1,count(v1)c1,grouping_id(g1,g2,g3)gid from t group by cube(g1,g2,g3);

已解释。

已用时间:  00: 00: 00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2755641042

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |       |   107K|  5456K|   350   (2)| 00:00:05 |
|   1 |  LOAD AS SELECT        | TCUBE |       |       |            |          |
|   2 |   SORT GROUP BY        |       |   107K|  5456K|   208   (2)| 00:00:03 |
|   3 |    GENERATE CUBE       |       |   107K|  5456K|   208   (2)| 00:00:03 |
|   4 |     SORT GROUP BY      |       |   107K|  5456K|   208   (2)| 00:00:03 |
|   5 |      TABLE ACCESS FULL | T     |   107K|  5456K|   205   (1)| 00:00:03 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

已选择16行。

已用时间:  00: 00: 00.07
SQL>
SQL> explain plan for
  2  create table tcube2 as select G1,G2,G3,count(v1)c1,sum(v1)s1,0 gid from t group by G1,G2,G3 union all
  3  select null G1,G2,G3,count(v1)c1,sum(v1)s1,1 gid from t group by null,G2,G3               union all
  4  select G1,null G2,G3,count(v1)c1,sum(v1)s1,2 gid from t group by G1,null,G3               union all
  5  select null G1,null G2,G3,count(v1)c1,sum(v1)s1,3 gid from t group by null,null,G3        union all
  6  select G1,G2,null G3,count(v1)c1,sum(v1)s1,4 gid from t group by G1,G2,null               union all
  7  select null G1,G2,null G3,count(v1)c1,sum(v1)s1,5 gid from t group by null,G2,null        union all
  8  select G1,null G2,null G3,count(v1)c1,sum(v1)s1,6 gid from t group by G1,null,null        union all
  9  select null G1,null G2,null G3,count(v1)c1,sum(v1)s1,7 gid from t group by null,null,null ;

已解释。

已用时间:  00: 00: 00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2902273211

---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |        |   859K|    26M|  2396  (86)| 00:00:29 |
|   1 |  LOAD AS SELECT        | TCUBE2 |       |       |            |          |
|   2 |   UNION-ALL            |        |       |       |            |          |
|   3 |    HASH GROUP BY       |        |   107K|  5456K|   208   (2)| 00:00:03 |
|   4 |     TABLE ACCESS FULL  | T      |   107K|  5456K|   205   (1)| 00:00:03 |
|   5 |    HASH GROUP BY       |        |   107K|  4092K|   208   (2)| 00:00:03 |
|   6 |     TABLE ACCESS FULL  | T      |   107K|  4092K|   205   (1)| 00:00:03 |
|   7 |    HASH GROUP BY       |        |   107K|  4092K|   208   (2)| 00:00:03 |
|   8 |     TABLE ACCESS FULL  | T      |   107K|  4092K|   205   (1)| 00:00:03 |
|   9 |    HASH GROUP BY       |        |   107K|  2728K|   208   (2)| 00:00:03 |
|  10 |     TABLE ACCESS FULL  | T      |   107K|  2728K|   205   (1)| 00:00:03 |
|  11 |    HASH GROUP BY       |        |   107K|  4092K|   208   (2)| 00:00:03 |
|  12 |     TABLE ACCESS FULL  | T      |   107K|  4092K|   205   (1)| 00:00:03 |
|  13 |    HASH GROUP BY       |        |   107K|  2728K|   208   (2)| 00:00:03 |
|  14 |     TABLE ACCESS FULL  | T      |   107K|  2728K|   205   (1)| 00:00:03 |
|  15 |    HASH GROUP BY       |        |   107K|  2728K|   208   (2)| 00:00:03 |
|  16 |     TABLE ACCESS FULL  | T      |   107K|  2728K|   205   (1)| 00:00:03 |
|  17 |    SORT GROUP BY NOSORT|        |   107K|  1364K|   205   (1)| 00:00:03 |
|  18 |     TABLE ACCESS FULL  | T      |   107K|  1364K|   205   (1)| 00:00:03 |
---------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

已选择29行。

已用时间:  00: 00: 00.04
SQL>
SQL> explain plan for
  2  create table tcube3 as
  3  with s as
  4  (select G1,G2,G3,count(v1)c1,sum(v1)s1,0 gid from t group by G1,G2,G3)
  5  select * from s union all
  6  select null G1,G2,G3,sum(c1)c1,sum(s1)s1,1 gid from s group by null,G2,G3               union all
  7  select G1,null G2,G3,sum(c1)c1,sum(s1)s1,2 gid from s group by G1,null,G3               union all
  8  select null G1,null G2,G3,sum(c1)c1,sum(s1)s1,3 gid from s group by null,null,G3        union all
  9  select G1,G2,null G3,sum(c1)c1,sum(s1)s1,4 gid from s group by G1,G2,null               union all
10  select null G1,G2,null G3,sum(c1)c1,sum(s1)s1,5 gid from s group by null,G2,null        union all
11  select G1,null G2,null G3,sum(c1)c1,sum(s1)s1,6 gid from s group by G1,null,null        union all
12  select null G1,null G2,null G3,sum(c1)c1,sum(s1)s1,7 gid from s group by null,null,null ;

已解释。

已用时间:  00: 00: 00.03
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1752036118

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT     |                           |   214K|  9759K|  3828  (87)| 00:00:46 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           |                           |       |       |            |          |
|   3 |    HASH GROUP BY           |                           |   107K|  5456K|   208   (2)| 00:00:03 |
|   4 |     TABLE ACCESS FULL      | T                         |   107K|  5456K|   205   (1)| 00:00:03 |
|   5 |   LOAD AS SELECT           | TCUBE3                    |       |       |            |          |
|   6 |    UNION-ALL               |                           |       |       |            |          |
|   7 |     VIEW                   |                           |   107K|  7030K|   350   (1)| 00:00:05 |
|   8 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
|   9 |     HASH GROUP BY          |                           |     5 |   260 |   354   (2)| 00:00:05 |
|  10 |      VIEW                  |                           |   107K|  5456K|   350   (1)| 00:00:05 |
|  11 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
|  12 |     HASH GROUP BY          |                           |     5 |   260 |   354   (2)| 00:00:05 |
|  13 |      VIEW                  |                           |   107K|  5456K|   350   (1)| 00:00:05 |
|  14 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
|  15 |     HASH GROUP BY          |                           |     3 |   117 |   354   (2)| 00:00:05 |
|  16 |      VIEW                  |                           |   107K|  4092K|   350   (1)| 00:00:05 |
|  17 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
|  18 |     HASH GROUP BY          |                           |     5 |   260 |   354   (2)| 00:00:05 |
|  19 |      VIEW                  |                           |   107K|  5456K|   350   (1)| 00:00:05 |
|  20 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
|  21 |     HASH GROUP BY          |                           |     3 |   117 |   354   (2)| 00:00:05 |
|  22 |      VIEW                  |                           |   107K|  4092K|   350   (1)| 00:00:05 |
|  23 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
|  24 |     HASH GROUP BY          |                           |     3 |   117 |   354   (2)| 00:00:05 |
|  25 |      VIEW                  |                           |   107K|  4092K|   350   (1)| 00:00:05 |
|  26 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
|  27 |     SORT GROUP BY NOSORT   |                           |   107K|  2728K|   350   (1)| 00:00:05 |
|  28 |      VIEW                  |                           |   107K|  2728K|   350   (1)| 00:00:05 |
|  29 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6648_9C6244 |   107K|  5456K|   208   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

已选择40行。

已用时间:  00: 00: 00.07
SQL>

newkid

CBO自己的判断是GROUP BY CUBE代价最低。现在用的是dynamic sampling, 你把统计信息收集一下看看。

〇〇

分析了,执行计划没变
SQL> exec dbms_stats.gather_table_stats(ownname => 'PJPDATA',tabname => 'T' ,estimate_percent => null ,method_opt => 'for all columns'
);

PL/SQL 过程已成功完成。

已用时间:  00: 00: 03.84
SQL> explain plan for
  2  create table tcube as select g1,g2,g3,sum(v1)s1,count(v1)c1,grouping_id(g1,g2,g3)gid from t group by cube(g1,g2,g3);

已解释。

已用时间:  00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1684350304

----------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |       |   100K|  2343K|       |   980   (2)| 00:00:12 |
|   1 |  LOAD AS SELECT        | TCUBE |       |       |       |            |          |
|   2 |   SORT GROUP BY        |       |   100K|  2343K|       |   915   (2)| 00:00:11 |
|   3 |    GENERATE CUBE       |       |   100K|  2343K|       |   915   (2)| 00:00:11 |
|   4 |     SORT GROUP BY      |       |   100K|  2343K|  3552K|   915   (2)| 00:00:11 |
|   5 |      TABLE ACCESS FULL | T     |   100K|  2343K|       |   207   (2)| 00:00:03 |
----------------------------------------------------------------------------------------

已选择12行。

已用时间:  00: 00: 00.02
SQL> create table tcube as select g1,g2,g3,sum(v1)s1,count(v1)c1,grouping_id(g1,g2,g3)gid from t group by cube(g1,g2,g3);


表已创建。

已用时间:  00: 00: 03.85
SQL>

〇〇

用真实数据跑了1遍,结论没多大改变,cube最慢,已用时间:  00: 02: 49.33
直接union第二 已用时间:  00: 01: 54.00，
对临时表union第一 ，已用时间:  00: 01: 13.33

原始表 1107903 行

cube 7551850行

newkid

我把你9楼的例子做了一下，还是GROUP BY CUBE最快，1.14秒；
其他两种分别是1.59和1.22.

用AUTOTRACE看SELECT 部分的consistent gets，GROUP BY CUBE是340, 其他两种是1496,  2172。

〇〇

我再多测几次

〇〇

加并行参数后cube快了

SQL> create table tcube parallel 8 as select g1,g2,g3,g4,sum(v1)s1,count(v1)c1,grouping_id(g1,g2,g3,g4)gid from t group by cube(g1,g2,
g3,g4);

表已创建。

已用时间:  00: 00: 02.03
SQL> drop table tcube2 purge;

表已删除。

已用时间:  00: 00: 00.82
SQL> create table tcube2 parallel 8 as
s  2  elect G1,G2,G3,G4,count(v1)c1,sum(v1)s1,0 gid from t group by G1,G2,G3,G4                       union all
  3  select null G1,G2,G3,G4,count(v1)c1,sum(v1)s1,1 gid from t group by null,G2,G3,G4                union all
se  4  lect G1,null G2,G3,G4,count(v1)c1,sum(v1)s1,2 gid from t group by G1,null,G3,G4                union all
  5  select null G1,null G2,G3,G4,count(v1)c1,sum(v1)s1,3 gid from t group by null,null,G3,G4         union all
  6  select G1,G2,null G3,G4,count(v1)c1,sum(v1)s1,4 gid from t group by G1,G2,null,G4                union all
se  7  lect null G1,G2,null G3,G4,count(v1)c1,sum(v1)s1,5 gid from t group by null,G2,null,G4         union all
se  8  lect G1,null G2,null G3,G4,count(v1)c1,sum(v1)s1,6 gid from t group by G1,null,null,G4         union all
se  9  lect null G1,null G2,null G3,G4,count(v1)c1,sum(v1)s1,7 gid from t group by null,null,null,G4  union all
s 10  elect G1,G2,G3,null G4,count(v1)c1,sum(v1)s1,8 gid from t group by G1,G2,G3,null                union all
11  select null G1,G2,G3,null G4,count(v1)c1,sum(v1)s1,9 gid from t group by null,G2,G3,null         union all
s 12  elect G1,null G2,G3,null G4,count(v1)c1,sum(v1)s1,10 gid from t group by G1,null,G3,null        union all
se 13  lect null G1,null G2,G3,null G4,count(v1)c1,sum(v1)s1,11 gid from t group by null,null,G3,null union all
14  select G1,G2,null G3,null G4,count(v1)c1,sum(v1)s1,12 gid from t group by G1,G2,null,null        union all
sel 15  ect null G1,G2,null G3,null G4,count(v1)c1,sum(v1)s1,13 gid from t group by null,G2,null,null union all
16  select G1,null G2,null G3,null G4,count(v1)c1,sum(v1)s1,14 gid from t group by G1,null,null,null union all
select null G1,null G2,null G3,null G4,count(v1)c1,sum(v1)s1,15 gid from t group by null,null,null,null; 17

表已创建。

已用时间:  00: 00: 03.84

SQL> create table tcube3  parallel 8 as
  2  with s as
  3  (
  4  select G1,G2,G3,G4,count(v1)c1,sum(v1)s1,0 gid from t group by G1,G2,G3,G4
  5  )
s  6  elect * from s                       union all
select null G1,G2,G3,G4,sum(c1)c1,sum(s1)s1,1 gid from s group by null,G2,G3,G4                union all
select G1,null G2,G3,G4,sum(c1)c1,sum(s1)s1,2 gid from s group by G1,null,G3,G4                union all
select null G1,null G2,G3,G4,sum(c1)c1,sum(s1)s1,3 gid from s group by null,null,G3,G4         union all
select G1,G2,null G3,G4,sum(c1)c1,sum(s1)s1,4 gid from s group by G1,G2,null,G4                union all
select null G1,G2,null G3,G4,sum(c1)c1,sum(s1)s1,5 gid from s group by null,G2,null,G4         union all
select G1,null G2,null G3,G4,sum(c1)c1,sum(s1)s1,6 gid from s group by G1,null,null,G4         union all
select null G1,null G2,null G3,G4,sum(c1)c1,sum(s1)s1,7 gid from s group by null,null,null,G4  union all
select G1,G2,G3,null G4,sum(c1)c1,sum(s1)s1,8 gid from s group by G1,G2,G3,null                union all
select null G1,G2,G3,null G4,sum(c1)c1,sum(s1)s1,9 gid from s group by null,G2,G3,null         union all
select G1,null G2,G3,null G4,sum(c1)c1,sum(s1)s1,10 gid from s group by G1,null,G3,null        union all
select null G1,null G2,G3,null G4,sum(c1)c1,sum(s1)s1,11 gid from s group by null,null,G3,null union all
select G1,G2,null G3,null G4,sum(c1)c1,sum(s1)s1,12 gid from s group by G1,G2,null,null        union all
select null G1,G2,null G3,null G4,sum(c1)c1,sum(s1)s1,13 gid from s group by null,G2,null,null union all
select G1,null G2,null G3,null G4,sum(c1)c1,sum  7    8    9   10   11   12   13   14   15   16   17   18   19   20  (s1)s1,14 gid fro
m s group by G1,null,null,null union all
select null G1,null G2,null G3,nul 21  l G4,sum(c1)c1,sum(s1)s1,15 gid from s group by null,null,null,null;

表已创建。

已用时间:  00: 00: 05.47（比10楼不并行更慢）

[ 本帖最后由 〇〇 于 2010-2-10 10:05 编辑 ]