隐含参数_optimizer_skip_scan_enabled的作用

最新推荐文章于 2024-08-13 18:12:30 发布

事后诸葛亮

最新推荐文章于 2024-08-13 18:12:30 发布

阅读量1.3k

点赞数

分类专栏： parameters

parameters 专栏收录该内容

11 篇文章

订阅专栏

本文详细探讨了SQL优化器跳过扫描参数的设置如何影响执行计划，通过实例展示了参数开启与关闭时执行计划的区别，以及如何通过设置参数来优化查询效率。

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

当该参数被设置为false时，优化器将不会考虑索引跳跃扫描，如下测试：

SQL> alter session set "_optimizer_skip_scan_enabled"=false;

Session altered.

SQL> set autot traceonly explain

SQL> select
  2          /*+ index(t1) */
  3          small_vc
  4  from
  5          t1
  6  where
  7          n1      between 1 and 3
  8  and     ind_pad = rpad('x',40)
  9  and     n2      = 2
 10  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    36 |  2088 |   117   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    36 |  2088 |   117   (0)| 00:00:02 |
|*  2 |  INDEX RANGE SCAN          | T1_I1 |    36 |       |    82   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("N1">=1 AND "IND_PAD"='x
              ' AND "N2"=2 AND "N1"<=3)
       filter("N2"=2 AND "IND_PAD"='x
              ')

当将该参数设置为true时，优化器将会估算成本，如果索引跳跃扫描成本更低，将选择索引跳跃扫描的方式来产生执行计划：

SQL> alter session set "_optimizer_skip_scan_enabled"=true;

Session altered.

SQL> select
  2          /*+ index(t1) */
  3          small_vc
  4  from
  5          t1
  6  where
  7          n1      between 1 and 3
  8  and     ind_pad = rpad('x',40)
  9  and     n2      = 2
 10  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2886394002

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    36 |  2088 |    42   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    36 |  2088 |    42   (0)| 00:00:01 |
|*  2 |  INDEX SKIP SCAN           | T1_I1 |    36 |       |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("N1">=1 AND "IND_PAD"='x
              ' AND "N2"=2 AND "N1"<=3)
       filter("N2"=2 AND "IND_PAD"='x
              ')