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

 

posted @ 2013-05-08 14:55  孙愚  阅读(310)  评论(0编辑  收藏  举报