ORALCE /*+NO_EXPAND*/ 含义

求教 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

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

| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU
)| Time     |

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

|   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

 

评论

1 楼 JonHans 2012-03-13