备案控制台
探索云世界
开发者社区 华章出版社 文章 正文

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

2017-08-01 1140
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 本节书摘来自华章出版社《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中，主查询直接获取其数据。

文章标签：
SQL
索引
关系型数据库
Oracle
关键词：
SQL优化
SQL查询
Oracle数据库一键上云优化
SQL Oracle
Oracle数据库一键上云sql
华章计算机
目录
相关文章
1941623231718325
|
4天前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量：数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中，变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”，用于存储程序运行时的动态数据，通过`DECLARE`定义，可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”，一旦设定值便保持不变，用`CONSTANT`声明，提供程序稳定性和易维护性。通过 `%TYPE`、`NOT NULL`等特性，可以更高效地管理和控制变量与常量，提升代码质量。善用两者，能优化PL/SQL程序的结构和性能。
1941623231718325
13 1
changwoo
|
24天前
|
SQL 存储 关系型数据库
一文搞懂SQL优化——如何高效添加数据
**SQL优化关键点：** 1. **批量插入**提高效率，一次性建议不超过500条。 2. **手动事务**减少开销，多条插入语句用一个事务。 3. **主键顺序插入**避免页分裂，提升性能。 4. **使用`LOAD DATA INFILE`**大批量导入快速。 5. **避免主键乱序**，减少不必要的磁盘操作。 6. **选择合适主键类型**，避免UUID或长主键导致的性能问题。 7. **避免主键修改**，保持索引稳定。 这些技巧能优化数据库操作，提升系统性能。
changwoo
219 4
一文搞懂SQL优化——如何高效添加数据
阿里开发者
|
1天前
|
SQL 分布式计算 资源调度
一文解析 ODPS SQL 任务优化方法原理
本文重点尝试从ODPS SQL的逻辑执行计划和Logview中的执行计划出发，分析日常数据研发过程中各种优化方法背后的原理，覆盖了部分调优方法的分析，从知道怎么优化，到为什么这样优化，以及还能怎样优化。
阿里开发者
13 0
1941623231718325
|
4天前
|
存储 Oracle 数据管理
Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
【4月更文挑战第19天】Oracle 12c的ADO和热图技术革新数据管理。ADO智能清理无用数据，优化存储，提升查询速度，实现数据&quot;瘦身&quot;；热图则以直观的视觉表示展示数据分布和状态，助力识别性能瓶颈，犹如数据的&quot;透视&quot;工具。这两项技术结合，强化数据管理，为企业业务发展保驾护航。
1941623231718325
13 0
1941623231718325
|
4天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘，提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态，`%ROWCOUNT`记录处理行数，`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程，提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
1941623231718325
27 9
1941623231718325
|
4天前
|
SQL Oracle 安全
Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
【4月更文挑战第19天】Oracle PL/SQL中的循环语句（LOOP、EXIT WHEN、FOR、WHILE）是处理数据的关键工具，用于批量操作、报表生成和复杂业务逻辑。LOOP提供无限循环，可通过EXIT WHEN设定退出条件；FOR循环适用于固定次数迭代，WHILE循环基于条件判断执行。有效使用循环能提高效率，但需注意避免无限循环和优化大数据处理性能。掌握循环语句，将使数据处理更加高效和便捷。
1941623231718325
10 2
1941623231718325
|
4天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
【4月更文挑战第19天】在Oracle PL/SQL中，IF语句与CASE语句扮演着数据流程控制的关键角色。IF语句如红绿灯，依据条件决定程序执行路径；ELSE和ELSIF提供多分支逻辑。CASE语句则是分岔路，按表达式值选择执行路径。这些条件控制语句在数据验证、错误处理和业务逻辑中不可或缺，通过巧妙运用能实现高效程序逻辑，保障数据正确流转，支持企业业务发展。理解并熟练掌握这些语句的使用是成为合格数据管理员的重要一环。
1941623231718325
11 0
1941623231718325
|
4天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL表达式：数据的魔法公式
【4月更文挑战第19天】探索Oracle PL/SQL表达式，体验数据的魔法公式。表达式结合常量、变量、运算符和函数，用于数据运算与转换。算术运算符处理数值计算，比较运算符执行数据比较，内置函数如TO_CHAR、ROUND和SUBSTR提供多样化操作。条件表达式如CASE和NULLIF实现灵活逻辑判断。广泛应用于SQL查询和PL/SQL程序，助你驾驭数据，揭示其背后的规律与秘密，成为数据魔法师。
1941623231718325
13 2
LightOf
|
8天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化：如何发现SQL中的问题？
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划，关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列，以及区分度高的列。大数据表改结构需谨慎，可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例，如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量，可利用上一批的最大ID进行限制。
LightOf
33 3
洛神灬殇
|
25天前
|
SQL 关系型数据库 MySQL
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
洛神灬殇
39 1

华章出版社

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
  • 3
    【软件设计师备考 专题 】数据库语言（SQL）
  • 4
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 5
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 6
    SQL常见面试题总结2
  • 7
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 8
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 9
    数据库管理与电脑监控软件：SQL代码优化与实践
  • 10
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 1
    Oracle数据守卫（DG）：数据的“守护者”与“时光机”
    9
  • 2
    Oracle 12c的内存列存储：数据的“闪电侠”
    10
  • 3
    Oracle 12c的多重索引：数据的“多维导航仪”
    8
  • 4
    Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
    13
  • 5
    Oracle 12c多租户架构：数据管理的“摩天大楼”
    12
  • 6
    Oracle数据泵：数据迁移的魔法棒
    5
  • 7
    Oracle中的触发器与序列：自增列的魔法组合
    9
  • 8
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
    27
  • 9
    Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
    10
  • 10
    Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
    11

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • PolarDB for PostgreSQL 闪回特性体验
  • 使用SQL语句管理索引

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）