Databases Reference
In-Depth Information
Take the query in this example, which is as follows:
SQL> select id from mytab where last_name <> 'ALAPATI';
Execution Plan
----------------------------------------------------------
Plan hash value: 2134733830
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1739 | 19129 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYTAB| 1739 | 19129 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"<>'ALAPATI')
SQL>
The
<>(NOT EQUAL)
clause here results in the optimizer skipping the index on the
last_name
column. You
may try an
index
hint, as shown here:
SQL> select /*+ index (mytab name_idx) */ id from mytab where name <> 'ALAPATI'
Execution Plan
----------------------------------------------------------
Plan hash value: 332134091
----------------------------------------------------------------------------
| Id|Operation |Name | Rows | Bytes|Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1739 | 19129| 12 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID| MYTAB|1739 | 19129| 12 (0)| 00:00:01 |
|* 2| INDEX FULL SCAN |NAME_IDX | 1739 | | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"<>'ALAPATI')
The index hint made the optimizer replace a full table scan with an index full scan, which is better,
but not as good as an index range scan. An index full scan must read all the leaf nodes that contain the
value you specified with the
<>
operator, and thus is not a very efficient approach. (However, in other
cases, it may be possible for the optimizer to perform an index range scan as a result of your specifying
the
INDEX
hint.) The problem with specifying the
<>
condition is that the optimizer is likely to skip the
index even if the query returns a very small percentage of the rows in a table—it simply ignores any
indexes on the column in the
WHERE
predicate. Ignoring the index means that the optimizer will not even
calculate the costs of the index scan before producing the "optimal" plan; it figures it'll save all the
overhead involved in doing so because it assumes that the
<>
condition will result in the retrieval of a
large percentage of rows from the table. In cases such as this you can try to rewrite the query to avoid the
<>
operator.
