Data Warehouse Fundamentals I: Adding Indexes

Posted: April 16, 2014 in Data Warehouse Fundamentals

Welcome back, Oracle Enthusiasts!

Today, I am starting a new series entitled Data Warehouse Fundamentals. I have just completed reading the Oracle Documentation on the subject…

Oracle® Database 2 Day + Data Warehousing Guide
11g Release 2 (11.2)

It had some really good information and is a must read for any Oracle DBA who is shepherding a data warehouse. My only criticism is it focuses too much on the  Oracle Warehouse Builder.  My current job built a custom data warehouse that is similar to Oracle’s recommendations but customized for the healthcare industry. When things begin to go bad or stale you need to know the processes that populate the data warehouse so you can fix or tune the PLSQL behind the masterpiece. So it behooves the data warehouse DBA to know the code and not become reliant on Oracle Warehouse Builder.  This series will attempt to summarize the documentation and sprinkle in my personal experience.

Today’s lesson will focus on building good indexes (or indices) for the English majors out there.

At the heart of my data warehouse are partitioned tables and “swap” tables. A “swap” table is a non partitioned table that is loaded from a “fact” table into a specific table that will later be “swapped” into the partitioned table. You need a swap table for each partition of your partitioned table. There is an Oracle supplied package entitled EXCHANGE_PARTITION that does just that. In order for the swap to work, the table definition must be exactly the same to include indexes. If they are not, the indexes will be marked invalid and no longer usable by the process or any other query against the table. So, the proper thing to do is make sure your indexes are exactly the same in the partitioned table and the swap table. This is what I thought until I began to test the EXCHANGE_PARTITION process. Each partition exchange failed due to indexes not being identical.

Indexes, Local Indexes and Global Indexes

I was a little confused, here. After consulting my friend and colleague, Tim Gorman, he explained the process to me. The bottom line is Oracle views indexes differently between partitioned tables and non-partitioned tables. There are a couple of guidelines to follow that will help you immensely. This is by no means an exhaustive study on the subject. Tim has an excellent white paper on the subject that I highly recommend.   Look for “Scaling To Infinity: Partitioning Data Warehouses Using Oracle Database”

  1. Avoid using Global Indexes on partitioned tables. Part of the exchange partition code rebuilds global indexes. On a partitioned table with 30 million rows, this can be quite a lengthy endeavor. I say avoid, but if your business requirements include primary keys, that key will include a global unique index on the primary key column.
  2. Use a local index on the partitioned table and a standard index on the partitioned table. It’s magic! See below example…

local;                                                                                                                                      –THIS IS THE PARTITIONED TABLE

tablespace REPORTG_INDX
pctfree 10
initrans 2
maxtrans 255
initial 80K
next 1M
minextents 1
maxextents unlimited
);                                                                                                                                            –THIS IS THE SWAP TABLE

This looks very simple on paper and it really is, once you understand the inner workings behind Oracle’s approach to index usage. But it changed my life once I implemented it into my tables. Good luck and Happy Warehousing!!!




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s