Distinct Aggregate Transformation
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2011-08-31 02:43:30
Share to
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代码
- 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.
Trace the optimization process with 10053 event, we can found below message from the trace file.
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"
From these masseges, we can understand that the SQL is finnally transformed as below.
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 ---