我有一个具有以下结构的示例表:
create table mi_dimcustomer
(customer_num number(10),
<other columns> <data types>)
并且有一个unique index
on 列customer_num
。我试图hint the optimizer
不像这样使用这个索引(只是为了练习):
select /*+gather_plan_statistics*/ /*+ no_index(t idx1_dimcustomer) */
*
from mi_dimcustomer t
where t.customer_num = 12;
但是在执行计划中,我可以看到优化器还在使用索引!这是我捕获执行计划的方式:
Step-1)
select sql_id, child_number, sql_text
from v$sql
where sql_text like '%where t.CUSTOMER_NUM = 12%';
Step-2)
select *
from table(dbms_xplan.display_cursor('2qataxp9mahpj',
'0',
'ALLSTATS LAST +COST +OUTLINE'))
您可以在下面看到执行计划:
SQL_ID 2qataxp9mahpj, child number 0
-------------------------------------
select /*+gather_plan_statistics*//*+ NO_INDEX(t idx1_dimcustomer) */ *
from mi_dimcustomer t where t.CUSTOMER_NUM = 12
Plan hash value: 3784660444
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | IDX1_DIMCUSTOMER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------
为什么会这样?
提前致谢
优化器提示概述