group by rollup | cube 学习

1、如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

2、如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。


举例：SQL> select grouping(index_type) g_ind,grouping(status) g_st,index_type,status,count(*) from t group by rollup(index_type,status) order by 1,2;

G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB N/A 1
0 0 LOB VALID 587
0 0 FUNCTION-BASED NORMAL VALID 16
0 0 FUNCTION-BASED DOMAIN VALID 1
0 0 IOT - TOP VALID 103
0 0 CLUSTER VALID 10
0 0 NORMAL VALID 3171
0 0 NORMAL N/A 94
0 0 BITMAP VALID 1
0 1 FUNCTION-BASED NORMAL 16
0 1 NORMAL 3265
0 1 IOT - TOP 103
0 1 FUNCTION-BASED DOMAIN 1
0 1 BITMAP 1
0 1 CLUSTER 10
0 1 LOB 588
1 1 3984

17 rows selected




SQL> select grouping(index_type) g_ind,grouping(status) g_st,index_type,status,count(*) from t group by cube(index_type,status) order by 1,2;

G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB N/A 1
0 0 CLUSTER VALID 10
0 0 FUNCTION-BASED NORMAL VALID 16
0 0 NORMAL VALID 3171
0 0 IOT - TOP VALID 103
0 0 LOB VALID 587
0 0 FUNCTION-BASED DOMAIN VALID 1
0 0 BITMAP VALID 1
0 0 NORMAL N/A 94
0 1 IOT - TOP 103
0 1 CLUSTER 10
0 1 NORMAL 3265
0 1 BITMAP 1
0 1 LOB 588
0 1 FUNCTION-BASED NORMAL 16
0 1 FUNCTION-BASED DOMAIN 1
1 0 N/A 95
1 0 VALID 3889
1 1 3984

19 rows selected

http://blog.csdn.net/ghostgant/article/details/5699731