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.

---