Preventing Oracle from Using an Index(zt)

Preventing Oracle from Using an Index
Compliments of Oracle FAQ

In certain cases, you may want to disable the use of a specific, or all indexes for a given query. Here are some examples demonstrating several methods for doing so:

-- Adding an expression to the indexed column

SQL>select count(*) from t where empno+0=1000;

  COUNT(*)
----------
         1

Execution Plan
--------------------------------------------- ----- --------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

-- Specifying the FULL hint to force full table scan

SQL>select /*+ FULL(t) */ * from t where empno=1000;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------
      1000 Victor     DBA             7839 20-MAY-03      11000          0         10 JUNIOR

Execution Plan
--------------------------------------------- ----- --------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=41)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=41)

-- Specifying NO_INDEX hint

SQL>select /*+ NO_INDEX(T) */ count(*) from t where empno=1000;

  COUNT(*)
----------
         1

Execution Plan
--------------------------------------------- ----- --------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

-- Using a function over the indexed column

SQL>select count(*) from t where to_number(empno)=1000;

  COUNT(*)
----------
         1

Execution Plan
--------------------------------------------- ----- --------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

[@more@]

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

转载于:http://blog.itpub.net/94317/viewspace-796289/