Databases Reference
In-Depth Information
Note
You can't force the database to perform a parallel index range scan. However, the database does
perform a parallel index lookup when it performs a parallel nested loop join.
Your only option here is to rewrite your query to eliminate the
NOT EQUAL
clause. This is more true if
you have multiple predicates and less so when you're dealing with a single predicate. Remember that the
optimizer's behavior is the same when you specify the
NOT IN
clause as well. Another good solution in
some cases is to replace the
NOT EQUAL
predicate with a
CASE
construct. Remember that if you use a CASE
construct, you'd need a function-based index to match it.
Querying with Wild Characters
If you issue a query that includes a leading wildcard-based search, the optimizer is likely to ignore the
index and perform a full table scan. Take, for example, the following query:
SQL> select * from employees where last_name like'%lapati';
The optimizer is more likely to use an index when the leading character in the pattern is not % or an
underscore (_). Using a % or _ wildcard for the initial character with the
LIKE
operator means that the
database may have to read a significant proportion of a table's rows. If it were to use an index, it would
need to access every index block, and after the index reads were completed, it might also need to scan a
majority of the table blocks as well. A full table scan is potentially more efficient in this case. The
optimizer skips the index on the
last_name
column because it has to check each value in the column to
determine if it ends with the value "lapati". It ends up choosing a full table scan instead, as shown by the
following explain plan for this statement:
SQL> set autotrace on explain
SQL> select * from employees
2 where last_name like '%lapati';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 72 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME" LIKE '%lapati')
SQL>
