Saturday, May 31, 2008

How does one prevent Oracle from using an Index?

Autotrace Option:
------------------------

SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY: Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

Now look explain plan of a query which will use index.
SQL> SELECT COUNT(*) FROM TEST WHERE A=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2811351645

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX UNIQUE SCAN| SYS_C005994 | 1 | 3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Now we can prevent oracle from using index in several methods.

Method 1:Adding an expression to the indexed column:
------------------------------------------------------------------------
SQL> SELECT COUNT(*) FROM TEST WHERE A+0=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 160014765

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Method 2:Specifying the FULL hint to force full table scan:
--------------------------------------------------------------------------------------
SQL> select /*+ FULL(TEST)*/ * from TEST WHERE A=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 8 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Method 3:Specifying NO_INDEX hint
------------------------------------------------------------------
SQL> select /*+ NO_INDEX(TEST) */ count(*) from test where A=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Method 4:Using a function over the indexed column
-------------------------------------------------------------------

SQL> select count(*) from test where to_number(A)=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 160014765

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FULL SCAN| SYS_C005994 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

No comments:

Post a Comment