当push_pred,merge的cost值小于不转换的cost时才会采用

最新推荐文章于 2024-06-13 19:13:14 发布

launch_225

最新推荐文章于 2024-06-13 19:13:14 发布

阅读量884

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/launch_225/article/details/25565875

本文详细解析了SQL查询的执行计划优化过程，通过设置autotrace仅工具获取预估执行计划，对比不同SQL查询优化策略的影响，包括使用merge、push_pred等操作符对查询性能的影响，并分析了成本对执行计划选择的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



SQL> create table t1 as select * from dba_objects where object_id is not null;

Table created.

SQL> create index idx_t1_object_id on t1(object_id);

Index created.

SQL> create view v_t1 as select distinct object_id,object_name,object_type,status from t1;

View created.

SQL> 
SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> 
SQL> select  v1.status,v1.object_name,t2.object_name,t2.status
  2  from v_t1 v1,t2
  3  where v1.object_id=t2.object_id
  4  and v1.object_id in (88,9099);
--COST为9
Execution Plan
----------------------------------------------------------
Plan hash value: 4158291154

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |     2 |   240 |     9  (12)| 00:00:01 |
|   1 |  NESTED LOOPS                    |                  |       |       |            |          |
|   2 |   NESTED LOOPS                   |                  |     2 |   240 |     9  (12)| 00:00:01 |
|   3 |    VIEW                          | V_T1             |     2 |   168 |     5  (20)| 00:00:01 |
|   4 |     HASH UNIQUE                  |                  |     2 |   174 |     5  (20)| 00:00:01 |
|   5 |      INLIST ITERATOR             |                  |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| T1               |     2 |   174 |     4   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |     2 |       |     3   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN              | IDX_T2_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID    | T2               |     1 |    36 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("OBJECT_ID"=88 OR "OBJECT_ID"=9099)
   8 - access("V1"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T2"."OBJECT_ID"=88 OR "T2"."OBJECT_ID"=9099)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        854  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select /*+ merge(v1) */ v1.status,v1.object_name,t2.object_name,t2.status
  2  from v_t1 v1,t2
  3  where v1.object_id=t2.object_id
  4  and v1.object_id in (88,9099);

--COST也为9
Execution Plan
----------------------------------------------------------
Plan hash value: 3220906476

-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |     2 |   284 |     9  (12)| 00:00:01 |
|   1 |  VIEW                            | VM_NWVW_1        |     2 |   284 |     9  (12)| 00:00:01 |
|   2 |   HASH UNIQUE                    |                  |     2 |   270 |     9  (12)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                  |       |       |            |          |
|   4 |     NESTED LOOPS                 |                  |     2 |   270 |     8   (0)| 00:00:01 |
|   5 |      INLIST ITERATOR             |                  |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| T1               |     2 |   174 |     4   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |     2 |       |     3   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN            | IDX_T2_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID  | T2               |     1 |    48 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("OBJECT_ID"=88 OR "OBJECT_ID"=9099)
   8 - access("OBJECT_ID"="T2"."OBJECT_ID")
       filter("T2"."OBJECT_ID"=88 OR "T2"."OBJECT_ID"=9099)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        854  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> 
SQL> select /*+ push_pred(v1) */ v1.status,v1.object_name,t2.object_name,t2.status
  2  from v_t1 v1,t2
  3  where v1.object_id=t2.object_id
  4  and v1.object_id in (88,9099);

--COST较大
Execution Plan
----------------------------------------------------------
Plan hash value: 1517278093

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     2 |   218 |   225K (34)| 00:45:10 |
|   1 |  NESTED LOOPS                   |                  |     2 |   218 |   225K (34)| 00:45:10 |
|   2 |   INLIST ITERATOR               |                  |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID  | T2               | 75254 |  2645K|     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN            | IDX_T2_OBJECT_ID |     2 |       |     3   (0)| 00:00:01 |
|   5 |   VIEW PUSHED PREDICATE         | V_T1             |     1 |    73 |     3  (34)| 00:00:01 |
|   6 |    SORT UNIQUE                  |                  |     1 |    87 |     3  (34)| 00:00:01 |
|*  7 |     FILTER                      |                  |       |       |            |          |
|   8 |      TABLE ACCESS BY INDEX ROWID| T1               |     1 |    87 |     2   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | IDX_T1_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."OBJECT_ID"=88 OR "T2"."OBJECT_ID"=9099)
   7 - filter(88="T2"."OBJECT_ID" OR 9099="T2"."OBJECT_ID")
   9 - access("OBJECT_ID"="T2"."OBJECT_ID")
       filter("OBJECT_ID"=88 OR "OBJECT_ID"=9099)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        854  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
          
也就说明当cost大于等于原勿转换的SQL的cost时不会选择该计划；

另一侧面说明set autot traceonly工具生成的预估执行计划可以了解ORACLE的选择计划时的预估值的大小