下面介绍用于计算分析函数结果的操作。
(1)WINDOW (BUFFER)
分析函数(Analytic Function)在窗口内存中进行聚集计算。
关键词释义
分析函数(Analytic Function):基于一组数据计算出聚集结果。这样一组数据就称为一个窗口(window),由分析函数中的OVER ( ... )子句定义。
通过分析函数可以避免由复杂的执行操作(如关联)得出分析数据。示例如下:
HELLODBA.COM>exec sql_explain('select table_name, count(1) over (partition by table_name) cnt from
t_tables t', 'TYPICAL');
Plan hash value: 4083014614
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2070 | 37260 | 7 (0)| 00:00:07 |
| 1 | WINDOW BUFFER | | 2070 | 37260 | 7 (0)| 00:00:07 |
| 2 | INDEX FULL SCAN| T_TABLES_PK | 2070 | 37260 | 7 (0)| 00:00:07 |
--------------------------------------------------------------------------------
(2)WINDOW (SORT)
分析函数(Analytic Function)在窗口内存中对数据排序后,再进行聚集计算。
(3)WINDOW (NOSORT)
分析函数(Analytic Function)在窗口内存中对数据按照数据的物理顺序(或者数据结果有顺序要求,但数据物理顺序与逻辑顺序一致而无需再做排序)进行聚集计算。
(4)WINDOW (SORT PUSHED RANK)
子查询中的分析函数(Analytic Function)在窗口内存中对数据排序时,主查询中的谓词条件被推入子查询或视图中序列函数上,作为子查询或视图的过滤条件。示例如下:
HELLODBA.COM>exec sql_explain('select * from (select /*+index_ffs(T T_TABLES_IDX1)*/owner, row_number()
over (order by owner) rnum from t_tables t) where rnum<=10', 'TYPICAL');
Plan hash value: 603754030
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2070 | 62100 | 3 (34)| 00:00:04 |
|* 1 | VIEW | | 2070 | 62100 | 3 (34)| 00:00:04 |
|* 2 | WINDOW SORT PUSHED RANK| | 2070 | 12420 | 3 (34)| 00:00:04 |
| 3 | INDEX FAST FULL SCAN | T_TABLES_IDX1 | 2070 | 12420 | 2 (0)| 00:00:03 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER")<=10)
从以上执行计划中的谓词信息中可以看到PUSH之后的过滤条件。
(5)WINDOW (NOSORT STOPKEY)
分析函数(Analytic Function)在窗口内存中按照物理顺序处理数据时,处理特定记录数后停止。示例如下:
HELLODBA.COM>exec sql_explain('select * from (select /*+index(T T_TABLES_IDX1)*/owner, row_number()
over (order by owner) rnum from t_tables t) where rnum<=10', 'TYPICAL');
Plan hash value: 2520140641
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2070 | 62100 | 4 (0)| 00:00:04 |
|* 1 | VIEW | | 2070 | 62100 | 4 (0)| 00:00:04 |
|* 2 | WINDOW NOSORT STOPKEY| | 2070 | 12420 | 4 (0)| 00:00:04 |
| 3 | INDEX FULL SCAN | T_TABLES_IDX1 | 2070 | 12420 | 4 (0)| 00:00:04 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER")<=10)
(6)WINDOW(IN SQL MODEL (SORT))
依据模型化SQL语句中的规则,建立窗口,对数据排序,进行聚集计算。示例如下:
HELLODBA.COM>begin
2 sql_explain('SELECT statistic#, s
3 FROM t_sesstat
4 MODEL RETURN UPDATED ROWS
5 PARTITION BY (statistic#)
6 DIMENSION BY (sid)
7 MEASURES (value v, 1 s)
8 RULES
9 (
10 s[any] = sum(v) over (partition by statistic#)
11 )','TYPICAL');
12 end;
13 /
Plan hash value: 3747799581
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7280 | 65520 | 3 (0)| 00:00:04 |
| 1 | SQL MODEL ORDERED | | 7280 | 65520 | | |
| 2 | TABLE ACCESS FULL | T_SESSTAT | 7280 | 65520 | 3 (0)| 00:00:04 |
| 3 | WINDOW (IN SQL MODEL) SORT| | | | | |
-----------------------------------------------------------------------------------------