Databases Reference
In-Depth Information
The only way you can get the database to use the index in this case is to make sure at least one of the
two columns in this table is defined as
NOT NULL
, as shown here:
SQL>create table mytab1 (a int, b int NOT NULL);
Table created.
SQL> create unique index mytab1_idx on mytab1(a,b);
Index created.
SQL>
If all index columns are
NULL
, Oracle Database doesn't include rows into an index. However, you can
actually index
NULL
values by simply adding another column to the index, like so:
SQL> create index with_null on employees(nullable_column, '1');
Writing Functions in a Query
If a query contains a function on an indexed column in the
WHERE
clause, the optimizer skips the index.
Note that the optimizer ignores an index if you explicitly apply a function, or if the database applies a
function implicitly without your knowledge. Let's discuss the use of explicit functions first. For example,
say you issue the following query:
SQL> select * from testtab where UPPER(LAST_NAME)='ALAPATI';
In this case, the optimizer skips the index on the column
LAST_NAME
due to the presence of the
UPPER
function. (Chapter 5 explains how to use function-based indexes to get around this problem). If your
applications need to frequently apply a function to an indexed column, you're better off creating a
function-based index to allow the use of an index.
Note that even if a query doesn't explicitly apply a function to a column, it may be implicitly doing
so under some conditions. For example, if you define a number column in a table and then query the
column's values by specifying a character instead of a number, the optimizer ignores the index. That is,
if you issue the statement
select * from mytab where emp_id='999'
instead of
select * from mytab
where emp_id=999
, the optimizer needs to apply the
TO_NUMBER
function behind the scenes to get you the
answer. Again, the use of a function means that the optimizer will not use the index. Even if you specify
the
INDEX
hint in such a case, the optimizer might perform an index full scan, but not an index unique
scan. An index full scan has to scan the entire index, so it is much slower than an index unique scan.
Dates present many opportunities for implicit conversions to inhibit index use. It's very common to
see expressions such as the following:
SQL> select * from employees where trunc(start_date) = trunc(sysdate);
The use of the
TRUNC
function is often subconscious in writing statements like this. We truncate our dates
to eliminate time-of-day components, often without thinking of the consequences to query execution.
However, as far as Oracle Database is concerned, it only notices that you haven't actually indexed the
expression
TRUNC
(START_DATE)
; you have only indexed the
START_DATE
column. Therefore, the database
ignores the index.
A function-based index on the column
TRUNC (SYSDATE)
will make the optimizer choose the index,
but there's a simpler way to get around this problem without having to create the function-based index.
