045.【实验：多个提示的冲突测试】

【测试1】单个提示被使用了
SQL> set autot trace exp
SQL> select /*+ index_combine(t) */ * from scott.t_group1 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 342749274

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

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

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

| 0 | SELECT STATEMENT | | 4325K| 61M| 117K (1)
| 00:23:25 |

| 1 | TABLE ACCESS BY INDEX ROWID | T_GROUP1 | 4325K| 61M| 117K (1)
| 00:23:25 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | |
| |

| 3 | BITMAP INDEX FULL SCAN | IDX_USER_YN | | |
| |

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

【测试2】多个提示有冲突，所有提示都没有被使用
SQL> select /*+ index_combine(t) index_ffs(t) */ * from scott.t_group1 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4325K| 61M| 5682 (5)| 00:01:09 |
| 1 | TABLE ACCESS FULL| T_GROUP1 | 4325K| 61M| 5682 (5)| 00:01:09 |
------------------------------------------------------------------------------

--index_ffs(t)在前面
SQL> select /*+ index_ffs(t) index_combine(t) */ * from scott.t_group1 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4325K| 61M| 5682 (5)| 00:01:09 |
| 1 | TABLE ACCESS FULL| T_GROUP1 | 4325K| 61M| 5682 (5)| 00:01:09 |
------------------------------------------------------------------------------

【测试3】语法错误的提示写在后面，不会影响前面的提示
SQL> select /*+ index_combine(t) aindex_ffs(t) */ * from scott.t_group1 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 342749274

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

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

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

| 0 | SELECT STATEMENT | | 4325K| 61M| 117K (1)
| 00:23:25 |

| 1 | TABLE ACCESS BY INDEX ROWID | T_GROUP1 | 4325K| 61M| 117K (1)
| 00:23:25 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | |
| |

| 3 | BITMAP INDEX FULL SCAN | IDX_USER_YN | | |
| |

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

【测试4】语法错误的提示写在前面，会影响后面提示的执行
SQL> select /*+ aindex_ffs(t) index_combine(t IDX_USER_YN) */ * from scott.t_group1 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4325K| 61M| 5682 (5)| 00:01:09 |
| 1 | TABLE ACCESS FULL| T_GROUP1 | 4325K| 61M| 5682 (5)| 00:01:09 |
------------------------------------------------------------------------------

SQL> select /*+ aindex_ffs(t) index_combine(t IDX_USER_YN) */ * from scott.t_group1 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 4029886076

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4325K| 61M| 5682 (5)| 00:01:09 |
| 1 | TABLE ACCESS FULL| T_GROUP1 | 4325K| 61M| 5682 (5)| 00:01:09 |
------------------------------------------------------------------------------

[@more@]

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

转载于:http://blog.itpub.net/7901922/viewspace-1060028/