理解了rollup，那麼cube相對就好理解了，cube是對rollup的深度使用。cube是group by中幾個欄位的組合分組，其實要表示的典型的意思是dw中轉取的概念，每一個group by中要分組的欄位本身就是dw中的一個維度，而cube的出現把dw的含義體現的淋漓盡致.

rollup和grouping總結如下：

http://warehouse.itpub.net/post/777/524442

[@more@]

SQL> select status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by cube(status,owner,object_type)
4 order by status,owner,object_type
5 ;

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
INVALID PUBLIC SYNONYM 19
INVALID PUBLIC 19
INVALID TEST VIEW 1
INVALID TEST 1
INVALID TEST1 PROCEDURE 1
INVALID TEST1 1
INVALID PROCEDURE 1
INVALID SYNONYM 19
INVALID VIEW 1
INVALID 21
VALID A TABLE 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID A 1
VALID OUTLN INDEX 3
VALID OUTLN LOB 1
VALID OUTLN PROCEDURE 1
VALID OUTLN TABLE 3
VALID OUTLN 8
VALID PUBLIC SYNONYM 2491
VALID PUBLIC 2491
VALID TEST TABLE 3
VALID TEST 3
VALID TSMSYS INDEX 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID TSMSYS LOB 1
VALID TSMSYS TABLE 1
VALID TSMSYS 3
VALID INDEX 4
VALID LOB 2
VALID PROCEDURE 1
VALID SYNONYM 2491
VALID TABLE 8
VALID 2506
A TABLE 1
A 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
OUTLN INDEX 3
OUTLN LOB 1
OUTLN PROCEDURE 1
OUTLN TABLE 3
OUTLN 8
PUBLIC SYNONYM 2510
PUBLIC 2510
TEST TABLE 3
TEST VIEW 1
TEST 4
TEST1 PROCEDURE 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
TEST1 1
TSMSYS INDEX 1
TSMSYS LOB 1
TSMSYS TABLE 1
TSMSYS 3
INDEX 4
LOB 2
PROCEDURE 2
SYNONYM 2510
TABLE 8
VIEW 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
2527

56 rows selected.

SQL>

上面查詢是分組欄位status,owner,object_type的組合分組之後的結果，status,owner,object_type這3個欄位的組合結果是：（status,owner,object_type），（status,owner），（status），（status,object_type），（owner,object_type），（object_type),另外還要注意的是每次分組之後都有小計（rollup），理解了這個意思，那麼上面查詢的等價sql是：

SQL> select * from (
2 select status,owner,object_type,count(*) from dba_objects
3 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
4 group by rollup(status,owner,object_type)
5 union
6 select status,null,object_type,count(*) from dba_objects
7 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
8 group by rollup(status,object_type)
9 union
10 select null,owner,object_type,count(*) from dba_objects
11 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
12 group by rollup(owner,object_type)
13 union
14 select null,null,object_type,count(*) from dba_objects
15 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
16 group by rollup(object_type)
17 )
18 order by status,owner,object_type
19 ;