Databases Reference
In-Depth Information
If you modify the statement as shown next, the optimizer chooses an index scan.
SQL> select * from employees where last_name like 'alapati%';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1147874131
--------------------------------------------------------------------------------
|Id|Operation |Name |Rows|Bytes|Cost(%CPU)|Time |
--------------------------------------------------------------------------------
| 0|SELECT SATEMENT | | 1| 72| 2 (0)|00:00:01|
| 1| TABLE ACCESS BY INDEX ROWID|EMPLOYEES | 1| 72| 2 (0)|00:00:01|
|*2| INDEX RANGE SCAN | EMPLOYEES_IDX1| 1| | 1 (0)|00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME" LIKE 'alapati%')
filter("LAST_NAME" LIKE 'alapati%')
SQL>
Note that the first example uses a leading wildcard-based search. In fact, to force the use of an
index, moving the wildcard (%) from the leading position by even one space (
select * from employees
where last_name like 'a%lapati%
) makes the optimizer use the index EMPLOYEES_IDX1 on the
EMPLOYEES table in the example. The use of wildcards later in the search string doesn't inhibit index
use as frequently as this example demonstrates. So how many characters before the % will allow the
index to be used? As additional characters are added to the search string prior to the specification of the
wildcard (%,_), the optimizer naturally expects the database to read fewer index and table rows and it's
more likely to choose the index. Thus, if you're searching for “ABC%” instead of “%ABC”, the database
will be more likely to use an index range scan, so long it figures that a full table scan is more expensive.
Referencing Null Values in Predicates
Suppose you have a table with two columns, both of which are allowed to have
NULL
values. Let's also
assume that there are, in fact, several rows in the table that do have
NULLs
in both rows. Say you issue the
following statement:
SQL> select * from mytable where a is null;
The optimizer won't use a unique index on this table because anytime you have
NULL
values for all
columns of a unique index, the database lets you add the row to the table, but doesn't include it in the
index. In fact, you can add multiple rows with
NULL
values for all the columns, even if you have a unique
index on the table, because Oracle Database considers two rows with all
NULL
values as different so far as
uniqueness is considered. The result is that the table will have more values than the index, because the
rows with all
NULL
values for its columns won't be inserted into the index. When you issue the previous
query, the database ignores the index since that index doesn't include the rows with all
NULL
values. To
avoid giving you a wrong answer, the database ignores the index.
