The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Rebuilding the Unbalanced Indexes
So, what is the solution? Oracle
version 7.2 and above provides a facility that rebuilds an index
in-place. This is done by issuing an ALTER INDEX command:
INDEX xxx REBUILD TABLESPACE yyy;
You must specify the tablespace name when using this command. If
the tablespace name is not present, Oracle will attempt to rebuild
the index in the default tablespace name of the connected user who
is issuing the command.
Let’s take a look at what happens in
an in-place rebuild of an Oracle index. The index will be rebuilt
in-place with the same number of extents as the original index.
Hence, this command is not useful for reorganizing an index into a
single extent. Also, each index node will be rebuilt in-place and
the excessive levels and deleted leaf rows will be fixed. Since the
goal of the data warehouse manager is to rebuild only those indexes
that have more than three levels or lot’s of deleted leaf rows, we
have the id5.sql script generate the rebuild commands directly from
the temp_stat table and stored the output as id6.sql.
While Oracle does not publish how the rebuild command works
internally, you must have extra space, equal to the index size, in
each tablespace in order to issue the rebuild command. If Oracle
cannot get enough scratch space in the target tablespace, the
existing index will remain intact, and you will receive the message
FAILED TO ALLOCATE AN EXTENT OF SIZE xxx IN TABLESPACE yyy.
This is an excerpt from "High Performance
Data Warehousing", copyright 1997.
like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive
Reference , with
over 900 pages of BC's favorite tuning tips &
You can buy it
directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning