DISTINCT聚集函数转换

在Oracle 10gR2中，引入了哈希分组的方法来计算聚集函数，在数据量比较大时，这种方法比传统的排序分组方法效率更高。但是，对于那些对唯一值（含有DISTINCT/UNIQUE）的聚集函数，由于要消除重复值，因此还是要使用排序分组的方法。在11gR2，引入了一项新的查询转换技术，将含有DISTINCT的聚集函数转换为一个聚集内联视图，使得查询同样可以利用哈希分组的方法来计算聚集函数。

    注意：DISTINCT聚集函数转换可以由参数"_optimizer_distinct_agg_transform"或提示TRANSFORM_DISTINCT_AGG/NO_TRANSFORM_DISTINCT_AGG控制。

示例（11gR2）：

SQL代码

1. HELLODBA.COM>exec sql_explain('select owner, avg(avg_row_len), count(distinct table_name) from t_tables group by owner', 'TYPICAL OUTLINE');  
2. Plan hash value: 1728567205  
3.   
4. ---------------------------------------------------------------------------------  
5. | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
6. ---------------------------------------------------------------------------------  
7. |   0 | SELECT STATEMENT     |          |    18 |   900 |    31   (4)| 00:00:01 |  
8. |   1 |  HASH GROUP BY       |          |    18 |   900 |    31   (4)| 00:00:01 |  
9. |   2 |   VIEW               | VW_DAG_0 |  2696 |   131K|    31   (4)| 00:00:01 |  
10. |   3 |    HASH GROUP BY     |          |  2696 | 83576 |    31   (4)| 00:00:01 |  
11. |   4 |     TABLE ACCESS FULL| T_TABLES |  2696 | 83576 |    30   (0)| 00:00:01 |  
12. ---------------------------------------------------------------------------------  
13.   
14. Outline Data  
15. -------------  
16.   
17.   /*+  
18.       BEGIN_OUTLINE_DATA  
19.       USE_HASH_AGGREGATION(@"SEL$5771D262")  
20.       FULL(@"SEL$5771D262" "T_TABLES"@"SEL$1")  
21.       USE_HASH_AGGREGATION(@"SEL$C33C846D")  
22.       NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")  
23.       TRANSFORM_DISTINCT_AGG(@"SEL$1")  
24.       OUTLINE(@"SEL$5771D262")  
25.       OUTLINE(@"SEL$1")  
26.       OUTLINE_LEAF(@"SEL$C33C846D")  
27.       TRANSFORM_DISTINCT_AGG(@"SEL$1")  
28.       OUTLINE_LEAF(@"SEL$5771D262")  
29.       ALL_ROWS  
30.       DB_VERSION('11.2.0.1')  
31.       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')  
32.       IGNORE_OPTIM_EMBEDDED_HINTS  
33.       END_OUTLINE_DATA  
34.   */  
35.   
36. PL/SQL procedure successfully completed.  

    对上述语句做10053事件跟踪，可以看到以下转换信息：

SQL代码

1. DAGG_TRANSFORM: transforming query block SEL$1 (#0)  
2. qbcp (before transform):******* UNPARSED QUERY IS *******  
3. 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"  
4. pgactx->ctxqbc (before transform):******* UNPARSED QUERY IS *******  
5. 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"  
6. Registered qb: SEL$5771D262 0x1e94b604 (SPLIT QUERY BLOCK FOR DISTINCT AGG OPTIM SEL$1; SEL$1)  
7. ---------------------  
8. QUERY BLOCK SIGNATURE  
9. ---------------------  
10.   signature (): qb_name=SEL$5771D262 nbfros=1 flg=0  
11.     fro(0): flg=0 objn=73126 hint_alias="T_TABLES"@"SEL$1"  
12.   
13. Registered qb: SEL$C33C846D 0x21f4b74c (MAP QUERY BLOCK SEL$5771D262)  
14. ---------------------  
15. QUERY BLOCK SIGNATURE  
16. ---------------------  
17.   signature (): qb_name=SEL$C33C846D nbfros=1 flg=0  
18.     fro(0): flg=5 objn=0 hint_alias="VW_DAG_0"@"SEL$C33C846D"  
19.   
20. qbcp (after transform):******* UNPARSED QUERY IS *******  
21. 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"  
22. pgactx->ctxqbc (after transform):******* UNPARSED QUERY IS *******  
23. 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代码

1. SELECT "VW_DAG_0"."ITEM_2" "OWNER",  
2.        DECODE(NVL(SUM("VW_DAG_0"."ITEM_4"), 0),  
3.               0,  
4.               TO_NUMBER(NULL),  
5.               SUM("VW_DAG_0"."ITEM_3") / NVL(SUM("VW_DAG_0"."ITEM_4"), 0)) "AVG(AVG_ROW_LEN)",  
6.        COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTTABLE_NAME)"  
7.   FROM (SELECT "T_TABLES"."TABLE_NAME" "ITEM_1",  
8.                "T_TABLES"."OWNER" "ITEM_2",  
9.                SUM("T_TABLES"."AVG_ROW_LEN") "ITEM_3",  
10.                COUNT("T_TABLES"."AVG_ROW_LEN") "ITEM_4"  
11.           FROM "DEMO"."T_TABLES" "T_TABLES"  
12.          GROUP BY "T_TABLES"."TABLE_NAME", "T_TABLES"."OWNER") "VW_DAG_0"  
13.  GROUP BY "VW_DAG_0"."ITEM_2"