Re: partitioning in a data warehouse environment

I'll also add that if you're lucky enough to run on the Exadata platform, the Exadata V2 software includes a feature called Storage Index which is completely automatic and can help address some of the same cases that would normally require global indexes to address. The two aren't equivalent and there may be scenarios where global indexes are useful on Exadata, but storage indexes will likely help most of the time. I didn't see any mention of Exadata here, so I'll leave it at that for now.

Dan

On Tue, Nov 17, 2009 at 9:16 PM, Tim Gorman <tim_at_evdbt.com> wrote:

> 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

--
http://www.freelists.org/webpage/oracle-l