DISTINCT聚集函数转换
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
在Oracle 10gR2中，引入了哈希分组的方法来计算聚集函数，在数据量比较大时，这种方法比传统的排序分组方法效率更高。但是，对于那些对唯一值（含有DISTINCT/UNIQUE）的聚集函数，由于要消除重复值，因此还是要使用排序分组的方法。在11gR2，引入了一项新的查询转换技术，将含有DISTINCT的聚集函数转换为一个聚集内联视图，使得查询同样可以利用哈希分组的方法来计算聚集函数。
注意：DISTINCT聚集函数转换可以由参数"_optimizer_distinct_agg_transform"或提示TRANSFORM_DISTINCT_AGG/NO_TRANSFORM_DISTINCT_AGG控制。
示例（11gR2）：
SQL代码
- HELLODBA.COM>exec sql_explain('select owner, avg(avg_row_len), count(distinct table_name) from t_tables group by owner', 'TYPICAL OUTLINE');
- Plan hash value: 1728567205
- ---------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 18 | 900 | 31 (4)| 00:00:01 |
- | 1 | HASH GROUP BY | | 18 | 900 | 31 (4)| 00:00:01 |
- | 2 | VIEW | VW_DAG_0 | 2696 | 131K| 31 (4)| 00:00:01 |
- | 3 | HASH GROUP BY | | 2696 | 83576 | 31 (4)| 00:00:01 |
- | 4 | TABLE ACCESS FULL| T_TABLES | 2696 | 83576 | 30 (0)| 00:00:01 |
- ---------------------------------------------------------------------------------
- Outline Data
- -------------
- /*+
- BEGIN_OUTLINE_DATA
- USE_HASH_AGGREGATION(@"SEL$5771D262")
- FULL(@"SEL$5771D262" "T_TABLES"@"SEL$1")
- USE_HASH_AGGREGATION(@"SEL$C33C846D")
- NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
- TRANSFORM_DISTINCT_AGG(@"SEL$1")
- OUTLINE(@"SEL$5771D262")
- OUTLINE(@"SEL$1")
- OUTLINE_LEAF(@"SEL$C33C846D")
- TRANSFORM_DISTINCT_AGG(@"SEL$1")
- OUTLINE_LEAF(@"SEL$5771D262")
- ALL_ROWS
- DB_VERSION('11.2.0.1')
- OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
- IGNORE_OPTIM_EMBEDDED_HINTS
- END_OUTLINE_DATA
- */
- PL/SQL procedure successfully completed.
对上述语句做10053事件跟踪，可以看到以下转换信息：
SQL代码
- DAGG_TRANSFORM: transforming query block SEL$1 (#0)
- qbcp (before transform):******* UNPARSED QUERY IS *******
- SELECT "T_TABLES"."OWNER" "OWNER",AVG("T_TABLES"."AVG_ROW_LEN") "AVG(AVG_ROW_LEN)",COUNT(DISTINCT "T_TABLES"."TABLE_NAME") "COUNT(DISTINCTTABLE_NAME)" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."OWNER"
- pgactx->ctxqbc (before transform):******* UNPARSED QUERY IS *******
- SELECT "T_TABLES"."OWNER" "OWNER",AVG("T_TABLES"."AVG_ROW_LEN") "AVG(AVG_ROW_LEN)",COUNT(DISTINCT "T_TABLES"."TABLE_NAME") "COUNT(DISTINCTTABLE_NAME)" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."OWNER"
- Registered qb: SEL$5771D262 0x1e94b604 (SPLIT QUERY BLOCK FOR DISTINCT AGG OPTIM SEL$1; SEL$1)
- ---------------------
- QUERY BLOCK SIGNATURE
- ---------------------
- signature (): qb_name=SEL$5771D262 nbfros=1 flg=0
- fro(0): flg=0 objn=73126 hint_alias="T_TABLES"@"SEL$1"
- Registered qb: SEL$C33C846D 0x21f4b74c (MAP QUERY BLOCK SEL$5771D262)
- ---------------------
- QUERY BLOCK SIGNATURE
- ---------------------
- signature (): qb_name=SEL$C33C846D nbfros=1 flg=0
- fro(0): flg=5 objn=0 hint_alias="VW_DAG_0"@"SEL$C33C846D"
- qbcp (after transform):******* UNPARSED QUERY IS *******
- SELECT "VW_DAG_0"."ITEM_2" "OWNER",DECODE(NVL(SUM("VW_DAG_0"."ITEM_4"),0),0,TO_NUMBER(NULL),SUM("VW_DAG_0"."ITEM_3")/NVL(SUM("VW_DAG_0"."ITEM_4"),0)) "AVG(AVG_ROW_LEN)",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTTABLE_NAME)" FROM (SELECT "T_TABLES"."TABLE_NAME" "ITEM_1","T_TABLES"."OWNER" "ITEM_2",SUM("T_TABLES"."AVG_ROW_LEN") "ITEM_3",COUNT("T_TABLES"."AVG_ROW_LEN") "ITEM_4" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."TABLE_NAME","T_TABLES"."OWNER") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2"
- pgactx->ctxqbc (after transform):******* UNPARSED QUERY IS *******
- SELECT "VW_DAG_0"."ITEM_2" "OWNER",DECODE(NVL(SUM("VW_DAG_0"."ITEM_4"),0),0,TO_NUMBER(NULL),SUM("VW_DAG_0"."ITEM_3")/NVL(SUM("VW_DAG_0"."ITEM_4"),0)) "AVG(AVG_ROW_LEN)",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTTABLE_NAME)" FROM (SELECT "T_TABLES"."TABLE_NAME" "ITEM_1","T_TABLES"."OWNER" "ITEM_2",SUM("T_TABLES"."AVG_ROW_LEN") "ITEM_3",COUNT("T_TABLES"."AVG_ROW_LEN") "ITEM_4" FROM "DEMO"."T_TABLES" "T_TABLES" GROUP BY "T_TABLES"."TABLE_NAME","T_TABLES"."OWNER") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2"
最终，改写后的语句为：
SQL代码
- SELECT "VW_DAG_0"."ITEM_2" "OWNER",
- DECODE(NVL(SUM("VW_DAG_0"."ITEM_4"), 0),
- 0,
- TO_NUMBER(NULL),
- SUM("VW_DAG_0"."ITEM_3") / NVL(SUM("VW_DAG_0"."ITEM_4"), 0)) "AVG(AVG_ROW_LEN)",
- COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTTABLE_NAME)"
- FROM (SELECT "T_TABLES"."TABLE_NAME" "ITEM_1",
- "T_TABLES"."OWNER" "ITEM_2",
- SUM("T_TABLES"."AVG_ROW_LEN") "ITEM_3",
- COUNT("T_TABLES"."AVG_ROW_LEN") "ITEM_4"
- FROM "DEMO"."T_TABLES" "T_TABLES"
- GROUP BY "T_TABLES"."TABLE_NAME", "T_TABLES"."OWNER") "VW_DAG_0"
- GROUP BY "VW_DAG_0"."ITEM_2"
--- Fuyuncat ---