 |
|
Oracle Tips by Burleson |
Chapter 7 Oracle Index Design
Function-based indexes
One of the most important advances in Oracle
indexing is the introduction of function-based indexing.
Function-based indexes allow creation of indexes on expressions,
internal functions, and user-written functions in PL/SQL and Java.
Function-based indexes ensure that the Oracle designer is able to
use an index for its query. Prior to Oracle8, the use of a built-in
function would not be able to match the performance of an index.
Consequently, Oracle would perform the dreaded full-table scan.
Examples of SQL with function-based queries might include the
following:
Select * from
customer where substr(cust_name,1,4) = ‘BURL’;
Select * from customer where to_char(order_date,’MM’) = ’01;
Select * from customer where upper(cust_name) = ‘JONES’;
Select * from customer where initcap(first_name) = ‘Mike’;
Remember, Oracle always interrogates the WHERE
clause of the SQL statement to see if a matching index exists and
then evaluates the cost to see of the index is the lowest-cost
access method. By using function-based indexes, the Oracle designer
can create a matching index that exactly matches the predicates
within the SQL where clause. This ensures that the query is
retrieved with a minimal amount of disk I/O and the fastest possible
speed.
Index-only tables
Beginning with Oracle8, Oracle recognized that
a table with an index on every column did not require table rows. In
other words, Oracle recognized that by using a special table-access
method called an index fast full scan, the index could be queried
without actually touching the data itself.
Oracle codified this idea with its use of
index-only table (IOT) structure. When using an IOT, Oracle does not
create the actual table but instead keeps all of the required
information inside the Oracle index. At query time, the Oracle SQL
optimizer recognizes that all of the values necessary to service the
query exist within the index tree, at which time the Oracle
cost-based optimizer has a choice of either reading through the
index tree nodes to pull the information in sorted order or invoke
an index fast full scan, which will read the table in the same
fashion as a full table scan, using sequential prefetch (as defined
by the db_file_multiblock_read_count parameter). The multiblock read
facility allows Oracle to very quickly scan index blocks in linear
order, quickly reading every block within the index tablespace. The
listing below includes an example of the syntax to create an IOT.
CREATE TABLE
emp_iot (
emp_id number,
ename varchar2(20),
sal number(9,2),
deptno number,
CONSTRAINT pk_emp_iot_index PRIMARY KEY (emp_id) )
ORGANIZATION index
TABLESPACE spc_demo_ts_01
PCTHRESHOLD 20 INCLUDING ename;
Oracle dominates the market for relational
database technology, so Oracle designers must be aware of the
specialized index structures and fully understand how they can be
used to improve the performance of all Oracle SQL queries. Many of
these techniques are discussed in my book Oracle High-Performance
SQL Tuning (Oracle Press, 2001). This text details the process of
creating all of Oracle's index tree structures and offers
specialized tips and techniques for ensuring SQL queries are
serviced using the fastest and most efficient indexing structure.
 |
For more details and scripts, see 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. |
|
