《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一3.2 基于代价的查询转换-阿里云开发者社区

开发者社区> 华章出版社> 正文
登录阅读全文

《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一3.2 基于代价的查询转换

简介: 本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第3章,第3.2节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。

3.2 基于代价的查询转换

在进行基于代价的查询转换时,转换器先确认查询是否满足转换条件。一旦满足,就会对各种可行的转换方式进行枚举,并对它们进行代价估算,找到代价最低的方式。由此可见,相对于启发式查询转换,基于代价的查询转换是一个相当消耗资源(CPU和内存)的过程。
提示:Oracle中有一个优化器参数_OPTIMIZER_COST_BASED_TRANSFORMATION,用它来控制是否进行基于代价的查询转换,以及如何进行基于代价的查询转换,从而限制其对资源的消耗。

3.2.1 复杂视图合并

查询转换器对含有DISTINCT、GROUP BY的视图进行的合并称为复杂视图合并(Complex View Merging,CVM)。
提示:要进行复杂视图合并,需要确保视图合并(参数_complex_view_merging控制)功能和复杂视图合并功能(由优化器参数_complex_view_merging控制,默认为TRUE)都打开。

HELLODBA.COM>exec sql_explain('select * from t_users u, v_objects_sum o where u.username=o.owner and 
             u.created>:A', 'TYPICAL OUTLINE');
Plan hash value: 1302554469
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     9 |   819 |     3  (34)| 00:00:04 |
|   1 |  HASH GROUP BY                |                |     9 |   819 |     3  (34)| 00:00:04 |
|   2 |   NESTED LOOPS                |                |  4497 |   399K|     2   (0)| 00:00:03 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_USERS        |     2 |   172 |     1   (0)| 00:00:02 |
|*  4 |     INDEX RANGE SCAN          | T_USERS_IDX1   |     2 |       |     1   (0)| 00:00:02 |
|   5 |    BITMAP CONVERSION TO ROWIDS|                |  2163 | 10815 |     2   (0)| 00:00:03 |
|*  6 |     BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX4 |       |       |            |          |
------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2")
      LEADING(@"SEL$F5BB74E1" "U"@"SEL$1" "T_OBJECTS"@"SEL$2")
      BITMAP_TREE(@"SEL$F5BB74E1" "T_OBJECTS"@"SEL$2" AND(("T_OBJECTS"."OWNER")))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "U"@"SEL$1" ("T_USERS"."CREATED"))
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("U"."CREATED">:A)
   6 - access("U"."USERNAME"="OWNER")
在上例中,V_OBJECTS_SUM是含有GROUP BY子句的视图,与主查询合并后,视图中的对象与主查询中的对象直接关联。
3.2.2 关联谓词推入
关联谓词推入(Join Predicate Push-Down,JPPD)转换是基于代价的转换,如果没有特别说明,我们所说的关联谓词推入都是指新的关联谓词推入。
3.2.2.1 外关联的谓词推入
语句存在外关联匹配时,转换器可以将关联谓词条件推入视图的查询语句中,使之成为其子计划的访问谓词条件。
HELLODBA.COM>exec sql_explain('SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */* FROM t_tables t, v_objects_sys 
             v WHERE t.owner =v.owner(+) and t.table_name = v.object_name(+) AND t.tablespace_name = 
             :A', 'TYPICAL OUTLINE');
Plan hash value: 980895126
-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |  2033 |   567K|   558   (0)| 00:09:19 |
|   1 |  NESTED LOOPS OUTER            |                |  2033 |   567K|   558   (0)| 00:09:19 |
|*  2 |   TABLE ACCESS FULL            | T_TABLES       |   184 | 38272 |     6   (0)| 00:00:07 |
|   3 |   VIEW PUSHED PREDICATE        | V_OBJECTS_SYS  |     1 |    78 |     3   (0)| 00:00:04 |
|*  4 |    FILTER                      |                |       |       |            |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| T_OBJECTS      |     1 |    71 |     3   (0)| 00:00:04 |
|*  6 |      INDEX SKIP SCAN           | T_OBJECTS_IDX1 |     1 |       |     2   (0)| 00:00:03 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_SS(@"SEL$639F1A6F" "T_OBJECTS"@"SEL$2" ("T_OBJECTS"."STATUS" "T_OBJECTS"."OWNER"
              "T_OBJECTS"."OBJECT_NAME"))
      USE_NL(@"SEL$1" "V"@"SEL$1")
      LEADING(@"SEL$1" "T"@"SEL$1" "V"@"SEL$1")
      NO_ACCESS(@"SEL$1" "V"@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      PUSH_PRED(@"SEL$1" "V"@"SEL$1" 3 2)
      OUTLINE_LEAF(@"SEL$639F1A6F")
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."TABLESPACE_NAME"=:A)
   4 - filter('SYS'="T"."OWNER")
   6 - access("OWNER"="T"."OWNER" AND "OBJECT_NAME"="T"."TABLE_NAME")
       filter("OBJECT_NAME"="T"."TABLE_NAME" AND "OWNER"="T"."OWNER" AND "OWNER"='SYS')
上述查询中,关联条件"OWNER"="T"."OWNER" AND "OBJECT_NAME"="T"."TABLE_NAME"被推入了视图的查询语句中,从而成为了其子计划的访问条件。
3.2.2.2 联合查询视图中关联查询的谓词推入
转换器将关联条件推入含有联合操作(UNION或者UNION-ALL)查询的视图中,并使之成为联合查询视图中两个子查询的谓词。
HELLODBA.COM>exec sql_explain('select * from (select /*+index(t2 t_tables_pk)*/t2.owner, t2.table_name 
             from t_tables t2 union all select /*+index(t1 t_objects_pk)*/t1.owner, t1.object_name 
             from t_objects t1) v, t_users t4 where v.owner=t4.username and t4.user_id =:a and v.table_name
             like :b','TYPICAL OUTLINE');
Plan hash value: 316561174
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |    61 |  7686 |   585   (0)| 00:09:46 |
|   1 |  NESTED LOOPS                  |              |    61 |  7686 |   585   (0)| 00:09:46 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T_USERS      |     1 |    86 |     1   (0)| 00:00:02 |
|*  3 |    INDEX UNIQUE SCAN           | T_USERS_PK   |     1 |       |     1   (0)| 00:00:02 |
|   4 |   VIEW                         |              |     3 |   120 |   584   (0)| 00:09:45 |
|   5 |    UNION ALL PUSHED PREDICATE  |              |       |       |            |          |
|*  6 |     INDEX RANGE SCAN           | T_TABLES_PK  |     5 |   110 |     1   (0)| 00:00:02 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| T_OBJECTS    |   108 |  3024 |   583   (0)| 00:09:44 |
|   8 |      INDEX FULL SCAN           | T_OBJECTS_PK | 47585 |       |    60   (0)| 00:01:01 |
-----------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$639F1A6F" "T2"@"SEL$2" ("T_TABLES"."TABLE_NAME" "T_TABLES"."OWNER"))
      INDEX(@"SEL$B01C6807" "T1"@"SEL$3" ("T_OBJECTS"."OBJECT_ID"))
      USE_NL(@"SEL$1" "V"@"SEL$1")
      LEADING(@"SEL$1" "T4"@"SEL$1" "V"@"SEL$1")
      NO_ACCESS(@"SEL$1" "V"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T4"@"SEL$1" ("T_USERS"."USER_ID"))
      OUTLINE(@"SEL$1")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      PUSH_PRED(@"SEL$1" "V"@"SEL$1" 2)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$B01C6807")
      OUTLINE_LEAF(@"SEL$639F1A6F")
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T4"."USER_ID"=TO_NUMBER(:A))
   6 - access("T2"."TABLE_NAME" LIKE :B AND "T2"."OWNER"="T4"."USERNAME")
       filter("T2"."OWNER"="T4"."USERNAME" AND "T2"."TABLE_NAME" LIKE :B)
   7 - filter("T1"."OWNER"="T4"."USERNAME" AND "T1"."OBJECT_NAME" LIKE :B)
上述查询中,关联条件V."OWNER"="T4"."USERNAME"被推入了视图中,并成为联合查询视图中子查询的谓词。
3.2.3 谓词提取
所谓谓词提取(Predicate Pull Up),是指将视图(子查询)谓词中复杂的、代价高昂的过滤条件提取出来,放到主查询中进行过滤。
提示:谓词提取可以通过优化器参数_optimizer_filter_pred_pullup及提示pull_pred/no_pull_pred控制。
HELLODBA.COM>begin
  2     sql_explain('
  3     SELECT /*+qb_name(outv) */ owner, table_name, rownum
  4     FROM
  5     (
  6       SELECT /*+qb_name(inv)*/t.owner, t.table_name, t.last_analyzed
  7       FROM t_tables t
  8       WHERE (t.last_analyzed) < (SELECT /*+qb_name(subq)*/ MAX(created) FROM t_objects o)
  9       AND owner like ''A%''
 10       ORDER BY 1
 11     )v','TYPICAL OUTLINE PREDICATE');
 12  end;
 13  /
Plan hash value: 2416283887
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     2 |    86 |     5   (0)| 00:00:01 |
|   1 |  COUNT                        |                |       |       |            |          |
|*  2 |   VIEW                        |                |     2 |    86 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_TABLES       |     2 |    64 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T_TABLES_IDX1  |     2 |       |     2   (0)| 00:00:01 |
|   5 |    SORT AGGREGATE             |                |     1 |     8 |            |          |
|   6 |     INDEX FULL SCAN (MIN/MAX) | T_OBJECTS_IDX5 | 47585 |   371K|     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SUBQ" "O"@"SUBQ" ("T_OBJECTS"."CREATED"))
      INDEX_RS_ASC(@"SEL$AA570DA2" "T"@"INV" ("T_TABLES"."OWNER"))
      NO_ACCESS(@"SEL$9FF7933E" "V"@"OUTV")
      OUTLINE(@"OUTV")
      OUTLINE(@"INV")
      OUTLINE(@"SUBQ")
      OUTLINE_LEAF(@"SEL$9FF7933E")
      PULL_PRED(@"OUTV" "V" 1)
      OUTLINE_LEAF(@"SEL$AA570DA2")
      OUTLINE_LEAF(@"SUBQ")
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("LAST_ANALYZED"< (SELECT /*+ QB_NAME ("SUBQ") */ MAX("CREATED") FROM
              "T_OBJECTS" "O"))
   4 - access("OWNER" LIKE 'A%')
       filter("OWNER" LIKE 'A%')
从上述内容可见,视图中的复杂谓词条件被提取出来。
3.2.4 GROUP BY配置
GROUP BY配置(Placement)是一项用一个GROUP BY视图来替换复杂查询中的一个或多个表的优化技术。要进行GROUP BY配置的查询转换,需要满足以下条件:
1)外部主查询的FROM子句中最少有两张表;
2)外部主查询包含了GROUP BY的操作;
3)外部主查询中包含了一个对某张表的一个字段的聚集函数的查询。
提示:GROUP BY配置可以通过优化器参数“_optimizer_group_by_placement”或提示PLACE_GROUP_BY/NO_PLACE_GROUP_BY控制。
示例如下(以下示例在11.2.0.1中运行):
HELLODBA.COM>begin
  2   sql_explain('
  3          SELECT   /*+ qb_name(m) place_group_by(@m (t@m)) */
  4               owner, max(maxbytes)
  5          FROM t_tables t, t_datafiles d
  6          WHERE t.tablespace_name = d.tablespace_name
  7          GROUP BY t.owner ',
  8          'TYPICAL OUTLINE');
  9  end;
 10  /
Plan hash value: 4181908607
-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    18 |   792 |    33  (10)| 00:00:01 |
|   1 |  HASH GROUP BY        |             |    18 |   792 |    33  (10)| 00:00:01 |
|*  2 |   HASH JOIN           |             |    68 |  2992 |    32   (7)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T_DATAFILES |     6 |   180 |     3   (0)| 00:00:01 |
|   4 |    VIEW               | VW_GBF_1    |   102 |  1428 |    29   (7)| 00:00:01 |
|   5 |     HASH GROUP BY     |             |   102 |  1428 |    29   (7)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| T_TABLES    |  2696 | 37744 |    27   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$78EA785B")
      FULL(@"SEL$78EA785B" "T"@"M")
      USE_HASH_AGGREGATION(@"SEL$3D2A8CF5")
      USE_HASH(@"SEL$3D2A8CF5" "VW_GBF_1"@"SEL$30379648")
      LEADING(@"SEL$3D2A8CF5" "D"@"M" "VW_GBF_1"@"SEL$30379648")
      NO_ACCESS(@"SEL$3D2A8CF5" "VW_GBF_1"@"SEL$30379648")
      FULL(@"SEL$3D2A8CF5" "D"@"M")
      OUTLINE(@"M")
      OUTLINE(@"SEL$30379648")
      PLACE_GROUP_BY(@"M" ( "T"@"M" ) 1)
      OUTLINE_LEAF(@"SEL$3D2A8CF5")
      OUTLINE_LEAF(@"SEL$78EA785B")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITEM_1"="D"."TABLESPACE_NAME")
在上例中,由于进行了GROUP BY配置转换,执行计划先对表T_TABLES进行了GROUP BY操作,然后再与表T_DATAFILES关联。
3.2.5 表扩张
在对分区表进行查询时,可能发生这样的情形:查询条件能够命中分区表上的本地分区索引,但此时本地分区索引的某个分区的索引发生异常,导致该分区上的索引无法被使用。在11gR2之前,这种情况下,该分区索引将彻底无法使用;而在11gR2之后,引入了表扩张(Table Expansion,TE)的查询转换技术,使得优化器可以针对索引状态对不同分区评估是否使用索引。
提示:表扩张可以通过优化器参数“_optimizer_table_expansion”或提示EXPAND_TABLE/NO_EXPAND_TABLE控制。
示例如下(以下示例在11.2.0.1中运行):
HELLODBA.COM>alter index t_objects_list_idx3  modify partition part4 unusable;
Index altered.

HELLODBA.COM>exec sql_explain('select /*+EXPAND_TABLE(o)*/* from t_objects_list o','TYPICAL OUTLINE');
Plan hash value: 2631494874
----------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                |  7077 |  1430K|   305   (1)| 00:00:04 |       |       |
|   1 |  VIEW                   | VW_TE_1        |  7077 |  1430K|   305   (1)| 00:00:04 |       |       |
|   2 |   UNION-ALL             |                |       |       |            |          |       |       |
|   3 |    PARTITION LIST ALL   |                |  7076 |   691K|   300   (1)| 00:00:04 |     1 |  6    |
|*  4 |     TABLE ACCESS FULL   | T_OBJECTS_LIST |  7076 |   691K|   300   (1)| 00:00:04 |     1 |  6    |
|   5 |    PARTITION LIST SINGLE|                |     1 |    99 |     5   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|   6 |     TABLE ACCESS FULL   | T_OBJECTS_LIST |     1 |    99 |     5   (0)| 00:00:01 |     4 |  4    |
----------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SET$57F73675_2" "O"@"SEL$1")
      FULL(@"SET$57F73675_1" "O"@"SEL$1")
      NO_ACCESS(@"SEL$72AE2D8F" "VW_TE_1"@"SEL$72AE2D8F")
      OUTLINE(@"SEL$1")
      EXPAND_TABLE(@"SEL$1" "O"@"SEL$1")
      OUTLINE(@"SET$57F73675")
      OUTLINE_LEAF(@"SEL$72AE2D8F")
      EXPAND_TABLE(@"SEL$1" "O"@"SEL$1")
      OUTLINE_LEAF(@"SET$57F73675")
      OUTLINE_LEAF(@"SET$57F73675_1")
      OUTLINE_LEAF(@"SET$57F73675_2")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("O"."OWNER" IS NULL OR "O"."OWNER"<>'SYSTEM')
上述例子中,表T_OBJECTS_LIST是一张列举分区表,其中PART4的列举值是'SYSTEM'。
HELLODBA.COM>insert into tmp_lob select to_lob(high_value) from dba_ind_partitions where index_name = 
'T_OBJECTS_LIST_IDX3' and partition_name = 'PART4';

1 row created.

HELLODBA.COM>select to_char(b) from  tmp_lob;

TO_CHAR(B)
-------------------------------------------------------------------------------------------------------
'SYSTEM'
查询转换器按照本地索引t_objects_list_idx3在不同分区上的状态将语句重写为一个UNION-ALL查询。其中,第一个子查询是访问所有分区,并过滤掉索引状态为UNUSABLE的分区("O"."OWNER" IS NULL OR "O"."OWNER"<>'SYSTEM')上的数据;第二个子查询是对单个分区(PARTITION LIST SINGLE)的访问,由查询计划中Pstart & Pstop可以知道是访问PART4。
3.2.6 关联因式分解
关联因式分解(Join factorization,JF)是11gR2中引入的新的查询重写技术,它可以将UNION/UNION-ALL查询中的子查询合并为一个内联视图。示例参见代码清单3-16。
提示:关联因式分解可以通过优化器参数“_optimizer_join_factorization”或提示FACTORIZE_JOIN/NO_FACTORIZE_JOIN控制。
代码清单3-16 关联因式分解
HELLODBA.COM>begin
  2  sql_explain('
  3    select  /*+ qb_name(sb1) */ u.username, u.created, o.object_name from t_objects o, t_users u
  4    where o.owner=u.username and u.lock_date=:A
  5    union all
  6    select /*+ qb_name(sb2) */ u.username, u.created, o.object_name from t_objects o, t_users u
  7    where o.owner=u.username and u.lock_date=:B',
  8      'TYPICAL OUTLINE');
  9  end;
 10  /
Plan hash value: 3854854956

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    | 81521 |  5891K|   295   (1)| 00:00:04 |
|*  1 |  HASH JOIN           |                    | 81521 |  5891K|   295   (1)| 00:00:04 |
|   2 |   VIEW               | VW_JF_SET$A6672D85 |    26 |  1118 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL         |                    |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| T_USERS            |    13 |   325 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T_USERS            |    13 |   325 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL  | T_OBJECTS          | 72115 |  2183K|   288   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$FDE4A245" "U"@"SB2")
      FULL(@"SEL$3335C0C6" "U"@"SB1")
      USE_HASH(@"SEL$BCE2A4E7" "O"@"SB1")
      LEADING(@"SEL$BCE2A4E7" "VW_JF_SET$A6672D85"@"SEL$94FBCE2D" "O"@"SB1")
      FULL(@"SEL$BCE2A4E7" "O"@"SB1")
      NO_ACCESS(@"SEL$BCE2A4E7" "VW_JF_SET$A6672D85"@"SEL$94FBCE2D")
      OUTLINE(@"SET$1")
      OUTLINE(@"SB1")
      OUTLINE(@"SB2")
      OUTLINE(@"SEL$67A59F16")
      OUTLINE(@"SEL$E9EF0288")
      FACTORIZE_JOIN(@"SET$1"("O"@"SB1" "O"@"SB2"))
      OUTLINE(@"SET$27448025")
      OUTLINE(@"SEL$94FBCE2D")
      OUTLINE(@"SEL$4ECEF7CB")
      MERGE(@"SEL$67A59F16")
      OUTLINE_LEAF(@"SEL$BCE2A4E7")
      OUTLINE_LEAF(@"SET$A6672D85")
      OUTLINE_LEAF(@"SEL$3335C0C6")
      OUTLINE_LEAF(@"SEL$FDE4A245")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("O"."OWNER"="ITEM_1")
   4 - filter("U"."LOCK_DATE"=:A)
   5 - filter("U"."LOCK_DATE"=:B)
上述例子中,两个子查询被合并为一个对T_USERS查询的UNION-ALL内联视图,系统自动命名为VW_JF_SET$A6672D85,然后再与表T_OBJECTS做关联。
3.2.7 DISTINCT配置
对关联(JOIN)查询结果取DISTINCT值时,DISTINCT配置(Distinct Placement,DP)能用一个含有DISTINCT的内联视图对查询进行重写。这项查询重写技术在11gR2引入。
提示:DISTINCT配置可以由优化器参数“_optimizer_distinct_placement”或提示PLACE_DISTINCT/NO_PLACE_DISTINCT控制。
示例如下(以下示例在11.2.0.1中运行):
HELLODBA.COM>exec sql_explain('select /*+full(u) full(t) place_distinct*/distinct t.tablespace_name, 
             u.account_status from t_tables t, t_users u where t.owner=u.username', 'TYPICAL OUTLINE');
Plan hash value: 800024757
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |    12 |   480 |    36   (9)| 00:00:01 |
|   1 |  HASH UNIQUE          |                 |    12 |   480 |    36   (9)| 00:00:01 |
|*  2 |   HASH JOIN           |                 |   102 |  4080 |    35   (6)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T_USERS         |    31 |   806 |     3   (0)| 00:00:01 |
|   4 |    VIEW               | VW_DTP_1B35BA0F |   102 |  1428 |    31   (4)| 00:00:01 |
|   5 |     HASH UNIQUE       |                 |   102 |  1428 |    31   (4)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| T_TABLES        |  2696 | 37744 |    30   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$58BE4727")
      FULL(@"SEL$58BE4727" "T"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$6B08CE13")
      USE_HASH(@"SEL$6B08CE13" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
      LEADING(@"SEL$6B08CE13" "U"@"SEL$1" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
      NO_ACCESS(@"SEL$6B08CE13" "VW_DTP_1B35BA0F"@"SEL$1B35BA0F")
      FULL(@"SEL$6B08CE13" "U"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$1B35BA0F")
      PLACE_DISTINCT(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$6B08CE13")
      OUTLINE_LEAF(@"SEL$58BE4727")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ITEM_1"="U"."USERNAME")
上述例子中,语句被转换为以下查询:
SELECT /*+ PLACE_DISTINCT ) FULL ("U") */
DISTINCT "VW_DTP_1B35BA0F"."ITEM_2" "TABLESPACE_NAME",
         "U"."ACCOUNT_STATUS"       "ACCOUNT_STATUS"
  FROM (SELECT DISTINCT "T"."OWNER" "ITEM_1", "T"."TABLESPACE_NAME" "ITEM_2"
          FROM "DEMO"."T_TABLES" "T") "VW_DTP_1B35BA0F",
       "DEMO"."T_USERS" "U"
 WHERE "VW_DTP_1B35BA0F"."ITEM_1" = "U"."USERNAME"
3.2.8 WITH子查询转换
该转换包括:创建临时表,将WITH子查询结果写入临时表,主查询中直接获取临时表中的数据。因此这一转换也称为WITH子查询实体化(Materialize)。该转换由参数“_with_subquery”控制。
提示:WITH子查询转换可以由参数“_with_subquery”或提示MATERIALIZE/INLINE控制。
HELLODBA.COM>exec sql_explain('with v as (select /*+ MATERIALIZE qb_name(wv) */* from t_objects o where 
             object_id<:A) select count(*) from v', 'BASIC OUTLINE');
Plan hash value: 2309780835
------------------------------------------------------------------
| Id  | Operation                  | Name                        |
------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |
|   1 |  TEMP TABLE TRANSFORMATION |                             |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6601_F201F06C |
|   3 |    TABLE ACCESS FULL       | T_OBJECTS                   |
|   4 |   SORT AGGREGATE           |                             |
|   5 |    VIEW                    |                             |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6601_F201F06C |
------------------------------------------------------------------

上例中,WITH子查询的结果被写入了临时表SYS_TEMP_0FD9D6601_F201F06C中,主查询直接获取其数据。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: