 |
|
Oracle Concepts -
Special Non-Tree Indexes
Oracle Tips by Burleson Consulting |
Special Non-Tree Indexes
Oracle includes many new indexing algorithms that
dramatically increase the speed with which Oracle queries are serviced. This
section explores the internals of Oracle indexing; reviews the standard b-tree
index, bitmap indexes, function-based indexes, and index-only tables (IOTs); and
demonstrates how these indexes may dramatically increase the speed of Oracle SQL
queries.
Oracle uses indexes to avoid the need for large-table,
full-table scans and disk sorts, which are required when the SQL optimizer
cannot find an efficient way to service the SQL query. I begin our look at
Oracle indexing with a review of standard Oracle b-tree index methodologies.
While b-tree indexes are great for simple queries, they
are not very good for the following situations:
* Low-cardinality columns?columns with less than 200
distinct values do not have the selectivity required in order to benefit from
standard b-tree index structures.
* No support for SQL functions?B-tree indexes are not
able to support SQL queries using Oracle's built-in functions.
Oracle provides a variety of built-in functions that
allow SQL statements to query on a piece of an indexed column or on any one of a
number of transformations against the indexed column.
Prior to Oracle9i, the Oracle SQL optimizer had to
perform time-consuming long-table, full-table scans due to these shortcomings.
Consequently, it was no surprise when Oracle introduced more robust types of
indexing structures.
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It?s only $19.95 when you buy it directly from the
publisher
here.
 |
If you like Oracle tuning, you may enjoy the 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. |
|