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 Data Warehouse Design

Table PartitioningWhat are we going to do as the fact_table expands beyond normal table capacity? Let’s assume that our organization processes 20,000 orders daily, leading to 7.3 million rows per year. With Oracle’s efficient indexing, a table this large can create unique performance problems, primarily because the index must spawn many levels to properly index 7.3 million rows. Whereas a typical query might involve three index reads, a query against a 7 million row table might involve five index reads before the target row is fetched.

To alleviate this problem, many designers will use the concept of horizontal partitioning to split the tables into chunks by date. Here, we partition the table into smaller sub-tables, using the data as the distinguishing factor. As such, we may have a table for each month, with a name such as fact_table_1_97, fact_table_2_97, and so on.

Whenever we need to address multiple tables in a single operation, we can use the SQL UNION ALL statement to merge the tables together, as follows:

SELECT * FROM fact_table_1_97
UNION ALL
SELECT * FROM fact_table_2_97
UNION ALL
SELECT * FROM fact_table_3_97
ORDER BY order_year, order_month;

Note: In addition to having the benefit of smaller table indexes, this type of table partitioning combined with the UNION ALLstatement has the added benefit of allowing Oracle’s parallel query engine to simultaneously perform full-table scans on each of the sub-tables. In this case, a separate process would be invoked to process each of the three table scans. Oracle query manager would then gather the result data and sort it according to the ORDER BY clause. In the previous example, we could expect a 50 percent performance improvement over a query against a single fact_table.

For more information on data warehouse table partitioning, see Chapter 9, Distributed Oracle Data Warehouses.


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.
 

 


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.