use_concat，NO_EXPAND提示

USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块.
最后合并所有查询块的结果，返回结果集给用户。

但是从9.2.0后，这个提示似乎不是怎么有效了。

select /*+ use_concat optimizer_features_enable('9.0.1')*/
  2   *
  3    from wxh_tbd
  4   where OBJECT_ID=1 or OBJECT_name='table';

Execution Plan
----------------------------------------------------------
Plan hash value: 2270251816

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     2 |   186 |    44 |
|   1 |  CONCATENATION               |         |       |       |       |
|*  2 |   TABLE ACCESS FULL          | WXH_TBD |     1 |    93 |    41 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| WXH_TBD |     1 |    93 |     3 |
|*  4 |    INDEX RANGE SCAN          | T_D     |     1 |       |     2 |
------------------------------------------------------------------------

select /*+ use_concat optimizer_features_enable('9.2.0')*/
  2   *
  3    from wxh_tbd
  4   where OBJECT_ID=1 or OBJECT_name='table';

Execution Plan
----------------------------------------------------------
Plan hash value: 3295978849

-------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     2 |   186 |    42 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD |     2 |   186 |    42 |
-------------------------------------------------------------

select /*+ use_concat optimizer_features_enable('10.1.0')*/
  2   *
  3    from wxh_tbd
  4   where OBJECT_ID=1 or OBJECT_name='table';

Execution Plan
----------------------------------------------------------
Plan hash value: 3295978849

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     2 |   186 |    74   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD |     2 |   186 |    74   (0)| 00:00:01 |
-----------------------------------------------------------------------------

NO_EXPAND

对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.

select
  2   *
  3    from wxh_tbd
  4   where OBJECT_ID=1 or object_id is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 1905407575

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    43 |  3999 |     7   (0)| 00:00:01 |
|   1 |  CONCATENATION               |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| WXH_TBD |     1 |    93 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_D     |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| WXH_TBD |    42 |  3906 |     4   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T_D     |    42 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

select /*+NO_EXPAND*/
  2   *
  3    from wxh_tbd
  4   where OBJECT_ID=1 or object_id is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 3295978849

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    43 |  3999 |    74   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD |    43 |  3999 |    74   (0)| 00:00:01 |
-----------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-682869/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-682869/