ORALCE /+NO_EXPAND/ 含义

最新推荐文章于 2024-03-07 16:59:08 发布

bianxq

最新推荐文章于 2024-03-07 16:59:08 发布

阅读量725

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/bianxq/article/details/83442430

oracle SQL 专栏收录该内容

30 篇文章

订阅专栏

求教 ORALCE /*+NO_EXPAND*/ 含义是什么

求教 ORALCE /*+NO_EXPAND*/ 含义是什么谢谢
网上的解释是 "对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展."

但是什么是阻止其基于优化器对其进行扩展??有什么具体例子么,迷惑中
-------------------------------------------------------------------------------------

不对or做扩展

比如这样一种情况

select * from table column1=' ' or column2=' ';
如果column1和column2上都有索引,oracle就对or做扩展

你加了/*+ no_expand */ 不做扩展,就走全表了

你可以自己测试下
---------------------------------------------------------------------------------------

扩展是什么行为?
-------------------------------------

SQL> set autot trace
SQL> select * from t where object_id=10000 or object_name='a';

未选定行

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=104 Card=1115 Byte
      s=197355)

1 0 CONCATENATION
2 1    TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=100 C
      ard=560 Bytes=99120)

3 2    INDEX (RANGE SCAN) OF 'I_T_2' (INDEX) (Cost=1 Card=224
      )

4 1    TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=4 Car
      d=555 Bytes=98235)

5 4    INDEX (RANGE SCAN) OF 'I_T_1' (INDEX) (Cost=1 Card=224
      )

SQL>  select /*+ no_expand */ * from t where object_id=10000 or object_name='a';

未选定行

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=154 Card=8 Bytes=1
      416)

1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=8 Bytes=
      1416)
-----------------------------------------------------------------------------

扩展得意思就是：
比如你有where cola = 'A' and colb in ('B','C')
如果你不加NO-EXPAND ，优化器会给你优化成这样
where (cola ='A' and colb ='B') or (cola ='A' and colb ='C')
然后运用索引查找数据

后面优化得语句就事对上面原语句的扩展
-------------------------------------------------------------------------

SQL> set autot trace
SQL> select * from test where object_id=52171 or object_name='test';

128 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1876321401

--------------------------------------------------------------------------------
-------------

--------------------------------------------------------------------------------
-------------

| 0 | SELECT STATEMENT | | 26 | 2054 | 4 (0
)| 00:00:01 |

| 1 | CONCATENATION | | | |
| |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 13 | 1027 | 2 (0
)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | INX_OBJ_NAME | 5 | | 1 (0
)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID| TEST | 13 | 1027 | 2 (0
)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | INX_OBJ_ID | 5 | | 1 (0
)| 00:00:01 |

--------------------------------------------------------------------------------
-------------

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

   3 - access("OBJECT_NAME"='test')
   4 - filter(LNNVL("OBJECT_NAME"='test'))
   5 - access("OBJECT_ID"=52171)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
29 consistent gets
   0 physical reads
   0 redo size
       2162 bytes sent via SQL*Net to client
473 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
128 rows processed

SQL> select /*+no_expand*/* from test where object_id=52171 or object_name='test';

128 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation   | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT | |   128 | 10112 |     5   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |   128 | 10112 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=52171 OR "OBJECT_NAME"='test')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
   4 recursive calls
   0 db block gets
42 consistent gets
   0 physical reads
   0 redo size
       2162 bytes sent via SQL*Net to client
473 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
128 rows processed