 |
|
Oracle SQL Optimization with
function-based indexes
Oracle Tips by Burleson Consulting |
Oracle function-based indexes are a
super silver bullet for optimizing your SQL to do a minimum amount
of I/O to get the rows that you need.
Here are details on using
function based indexes. Also, see these related notes on
FBI:
Issues with function-based
indexes
One of the most common Oracle SQL tuning problems are full-table scans
that are imposed by invalidating a WHERE clause predicate with an
Oracle built-in Function (BIF). This problem is especially
prevalent when constraining SQL queries for date ranges because of the
intrinsic encryption of the relational database DATE datatype.
For example, these WHERE clause predicates might invoke an unexpected
full-table scan:
WHERE trunc(ship_date) > trunc(sysdate-7);
WHERE to_char(ship_date,’YYYY-MM-DD’) = ‘2004-01-04’;
Even though the ship_date column may
have an index, the trunc and to_char built-in functions will
invalidate the index, causing sub-optimal execution with unnecessary
I/O:
-
A less selective index would be
used.
-
A full-table scan might be
performed.
To avoid the index invalidation
issues you have several options:
1. Fix Predicate - You can rewrite the query to leave the
ship_date predicate alone:
WHERE ship_date >= trunc(ship_date-7) + 1;
WHERE ship_date = to_date(‘2004-01-04’,’YYYY-MM-DD’);
2. Call FBI - Create a function-based index on the predicate:
Create index trunc_ship_date on product(trunc(ship_date));
Create index char_ship_date on product(to_char(ship_date,’YYYY-MM-DD’);
3. Add Column - You could add a redundant column to the table.
This is an old Oracle7 trick that was used before the invention of
function-based indexes.
alter table product add trunc_ship_date varchar 40;
update product set trunc_ship_date = trunc(ship_date);
In sum, function-based indexes
(FBIs) are a Godsend for the Oracle DBA who has to remove unnecessary
full-table scans or fix sub-optimal index choices. Using an FBI can
positively effect all SQL statements that have the matching predicate
and speed-up an entire database.
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
