Oracle 分析函数的使用

核心提示： 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes=24871)1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)2 1 TABLE ACCESS (FU

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes=

24871)

1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)

2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871

)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

557 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

6 rows processed

从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.

1. 使用cube函数的介绍

Quote:

为了介绍cube函数我们再来看看另外一个使用rollup的例子

06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare

06:53:37 2 from t

06:53:38 3 group by rollup(area_code,bill_month)

06:53:49 4 /

AREA_CODE BILL_MONTH LOCAL_FARE

---------- --------------- --------------

5761 200405 13060433.89

5761 200406 13318931.01

5761 200407 13710265.93

5761 200408 14135782.21

5761 54225413.04

5762 200405 12643792.11

5762 200406 12795060.65

5762 200407 13224298.12

5762 200408 13376468.72