Re: partitioning in a data warehouse environment

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 17 Nov 2009 20:16:15 -0700
Message-ID: <4B0366FF.2070705_at_evdbt.com>

Sharon,

Try to use LOCAL indexes whenever possible, as your default choice.  They are easier to administer and make life easier in general.

GLOBAL indexes exist to correct some of the problems that might exist with LOCAL indexes.  GLOBAL indexes can either be invalidated or maintained during partition management operations, which can be controlled by the [ UPDATE | INVALIDATE ] GLOBAL INDEXES clause in the ALTER TABLE command (i.e. if not specified, INVALIDATE is the default); parallelism can be specified for this clause as well.  Updating (maintaining) global indexes during a SPLIT|MERGE|MOVE|EXCHANGE|TRUNCATE|DROP PARTITION operation can be fairly time-consuming and expensive in terms of resources, so in general GLOBAL indexes are a necessary evil.

When are GLOBAL indexes useful?  There are some situations where the use of LOCAL index might necessitate probes into each of the index partitions, which can add up to an expensive operation.  In that situation, GLOBAL indexes can prove more efficient.  Also, if a UNIQUE index does not contain the partition-key column(s), then creating that UNIQUE index as a LOCAL index will change its definition, so maintaining the intended definition of UNIQUE indexes which do not contain the partition-key column(s) is the second major reason to employ GLOBAL indexes instead of LOCAL indexes.

In my own opinion, only the first reason listed above (i.e. using GLOBAL indexes to make certain index scans more efficient than LOCAL indexes) is truly necessary.  With situations involving the second reason (i.e. enforcing uniqueness), it can be a long discussion, but I find that often people create unique indexes unnecessarily or for the wrong reasons, when there are often other methods by which uniqueness can be enforced, if it absolutely must be enforced.

Hope this helps....

Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim_at_EvDBT.com
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Lost Data?  http://www.ora600.be/

Sharon.Kovac_at_Rotometrics.com wrote:

We have a data warehouse running on version 11.1.0.7.  Our fact tables contain anywhere from 4 million to 41 million records.  We now want to partition some of the bigger, most frequently hit tables for both performance and maintenance.  I'm new to partitioning so I'm spending time analyzing the queries that are run against the tables and I have a good idea on how to partition each table.  

My question is regarding indexes.  I know that there are both local and global but I'm unsure when to use each.  It's my understanding that a local index only contains information for the records in one partition and a global has information about the records in the entire table, correct?  Are there some guidelines as to when to use each type?

Thanks,

Sharon

---------------------------------------------------------------------
This e-mail message is intended only for the personal use of the recipient(s) named above. This message is confidential. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify the sender immediately by e-mail and delete the original message.
---------------------------------------------------------------------

.

-- http://www.freelists.org/webpage/oracle-l Received on Tue Nov 17 2009 - 21:16:15 CST