Improve Oracle indexes ? Build faster, smaller and
Oracle Tips by Burleson Consulting
Note: For complete details on index create
performance, see my book "Oracle
Tuning: The Definitive Reference".
When using the create index syntax to build an
Oracle index, there are many options that can dramatically improve the
speed of the creation, the space used by the index, and the height of
the resulting index. Let?s review a few of these factors:
Index create speed: performance factors
? This option allows for parallel processes to scan the table. When an
index is created, Oracle must first collect the symbolic key/ROWID pairs
with a full-table scan. By making the full-table scan run in parallel,
the index creation will run many times faster, depending on the number
of CPUs, table partitioning and disk configuration. I recommend a n-1
for the degree option, where n is the number of CPUs on your
Oracle server. In this example we create an index on a 36 CPU server
and the index create twenty times faster:
? The nologging option bypasses the writing of the redo log,
significantly improving performance. The only danger with using
nologging is that you must re-run the create index syntax if
you perform a roll-forward database recovery. Using nologging
with create index can speed index creation by up to 30%
The nologging option is quite convoluted and dependent
on several factors.
- Database noarchivelog mode - If your database is in
"noarchivelog" mode and you are no using the APPEND hint for inserts,
you WILL STILL generate redo logs!
- Database archivelog mode - If you are in archivelog
mode, the table must be altered to nologging mode AND the SQL must be
using the APPEND hint. Else, redo WILL be generated.
Create index: Space & structure Factors
? The compress option is used to repress duplication of keys in
non-unique indexes. For concatenated indexes (indexes with multiple
columns), the compress option can reduce the size of the index by
more than half. The compress option allows you to specify the
prefix length for multiple column indexes. In this example we have a
non-unique index on several low cardinality columns (sex and
hair_color), and a high cardinality column (customer_id):
Tablespace blocksize option
? The blocksize of the index tablespace will have a huge impact on the
structure of the index. For details, read
Proof that large indexes reduce IO.
Here is an example of an index created in a 32k tablespace:
create tablespace 23k_ts
In sum, there are many parameters that you can use to
improve the performance of Oracle index creation, the size of the index
tree and the height of the tree structure.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.