HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Distinct Aggregate Transformation

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2011-08-31 02:43:30

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

Hash Group Aggregate was introduced in Oracle 11gR2. It's generally more efficient than the traditional aggregate method, Sort Group Aggregate, especially when compute much of data. However, for those functions with DISTINCT/UNIQUE keyword, for the sake of eliminating duplicated values, it should still adopt the traditional method. While in 11gR2, a new query transformation technical, Distinct Aggregate Transformation, was introduced. It can transform the aggregate functions on DISTINCT data to an aggregate inline view, which can benefit from Hash Group Aggregate.

Note: Distinct Aggregate Transformation can be enabled/disabled by the parameter "_optimizer_distinct_agg_transform", or hints TRANSFORM_DISTINCT_AGG/NO_TRANSFORM_DISTINCT_AGG.

Example (in 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.  

Trace the optimization process with 10053 event, we can found below message from the trace file.

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"  

From these masseges, we can understand that the SQL is finnally transformed as below.

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"  

--- Fuyuncat ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat