Re: Question About Indexes on a Partitioned Table

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:fKidnYCPtJ084eDbRVnyjAA_at_bt.com...
>
> A primary key can be enforced by a local index
> provided the key includes the partitioning column(s).
> The same is true of any unique index or index
> enforcing a unique constraint.
>
> Once that requirement is out of the way, the choice
> of local vs. global for ANY indexes on a partitioned
> table should largely be dictated by one pair of
> conflicting issues:
>
> Local indexes allow complete isolation of partitions,
> which means you can drop or exchange partitions
> virtually free of charge - but global indexes usually
> have to go through expensive maintenance.
>
> Global indexes allow maximum efficiency of access
> along the index path - but local indexes introduce
> access overheads for all queries that do NOT
> include the partitioning key, as every partition of
> the index will have to be probed.
>

Two additional questions: 1) does the partitioning key need to be indexed at all and 2) would using a prefixed local index provide better benefits than a non-prefixed local index?

>
> --
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
Received on Mon Jun 25 2007 - 18:38:59 CDT