Oracle 分析函数的使用

核心提示： Execution Plan--0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=24884)1 0 UNION-ALL2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=2487

Execution Plan

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

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=

24884)

1 0 UNION-ALL

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

3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=248

71)

4 1 SORT (AGGREGATE)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=170

17)

Statistics

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

0 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

561 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得出的汇总数据的例子

06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare

06:45:26 2 from t

06:45:30 3 group by rollup(nvl(area_code,'合计'))

06:45:50 4 /

AREA_CODE LOCAL_FARE

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

5761 54225413.04

5762 52039619.60

5763 69186545.02

5764 53156768.46

5765 104548719.19

333157065.31

6 rows selected.

Elapsed: 00:00:00.00

Execution Plan

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