"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message
news:nmYfi.27052$YL5.355_at_newssvr29.news.prodigy.net...
>
> 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?
>
- No - although any unique index (or index supporting a unique
constraint) has to include the partitioning key, which might then
appear as the first column of the index. The decision on whether
or not to index the partitioning key depends entirely on the usual
arguments for deciding whether or not to index a set of columns
- That depends purely on the nature of the queries. In general there
is no need to prefix local indexes. I believe this concept appeared
in 8.0 to workaround a limitation in the optimizer in that version.
Prefixing is just adding columns to an index in a certain column
order - you do it for just the same reasons you choose the set
of columns and column ordering for any other 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 Wed Jun 27 2007 - 08:13:05 CDT
