 |
|
Oracle Tips
by Burleson Consulting
|
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
How to create a clustered index
For Oracle data warehouses updates are usually done on a time-based
formula, so the issues becomes one of re-sequencing the index and
insuring that additions to the index are managed. The most obvious
key for an Oracle data warehouse would be the date column. Since
Oracle data warehouses are updated in batch mode periodically, the
physical sequence of the data with the index could be maintained if
we insure that the new records are pre-sorted in date order.
Note: A Date sequence for a
clustered index is only beneficial if your system performs a lot of
range scans by date.
Here are the options for appending
new rows onto the end of a physically sequenced Oracle table:
1. Pre-sort the extract files in
index key order and load with SQL*Loader.
2. Extract the data directly from
an Oracle OLTP system using date predicates:
Note: Oracle export/import utilities
have no mechanism for changing the physical sequence of tables, and
cannot be used to cluster an index.
Now that we understand the basic
constructs of Oracle indexes, let’s look at a dictionary query
(Listing 8.2) that will tell us the structure of our indexes. Note
that this query assumes that your Oracle database is using the
cost-based optimizer, and that your tables have been analyzed with
the ANALYZE TABLE
command. Here we see that the indexes are
grouped according to the tables that they are built upon. We also
see that the clustering factor for each index is computed as a
percentage of the number of rows in the index.
