BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 
 

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:

     INSERT INTO warehouse_table
           SELECT * FROM oltp_table@remote_instance
           SEE CODE DEPOT FOR FULL SCRIPT
           ORDER BY trans_date;

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.
This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.