Re: partitioning in a data warehouse environment

If your queries will span multiple partitions, then global indexes make
sense.
If your queries limited to specific partition (partitioning key is used in
where clause), then local indexes preferable.
Especially since global indexes require rebuild, when some partition
maintenance (i.e. drop partition) is performed.

Igor N.


On Mon, Nov 16, 2009 at 4:12 PM, <Sharon.Kovac@xxxxxxxxxxxxxxx> 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.
> ---------------------------------------------------------------------
>
> .
>

Other related posts: