| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Index Tablespace Issues
When a large data warehouse has been created, it is not uncommon to
see yearly aggregate values purged and re-computed on a scheduled
basis. When this happens, we know that the table and index extending
will not be a problem since the tablespace will coalesce all free
extents within the tablespace.
But what about the indexes? After a large purge, the indexes can
become out of balance, and additional I/O may be required to access
particular records. The logical remedy to out-of-balance indexes
would be to drop and rebuild all of the Oracle indexes. However,
this poses another problem. To understand this process, let’s
examine a snapshot of an index tablespace prior to dropping all
indexes in the tablespace (Figure 8.8)
Figure 8.8 An Oracle Index Tablespace as seen by Oracle
Tablespace Manager
Now, lets drop all of the indexes, and look at the tablespace after
the drop (Figure 8.9).
Figure 8.9 An Oracle Index Tablespace after dropping an index
Here we see that there are lots of empty extents in the tablespace,
since the empty storage has not been coalesced. Now, if we are on
Oracle 7.2 or above we can issue the ALTER TABLESPECE XXX COALESCE
command (Figure 8.10). Now, the tablespace is clear, and the index
can easily be re-created to reside in a single extent.
Figure 8.10 An Oracle Index Tablespace after coalescing the
tablespace
|
