Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Question About Indexes on a Partitioned Table

Re: Question About Indexes on a Partitioned Table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 27 Jun 2007 14:13:05 +0100
Message-ID: <VqOdndhdMNTD_x_bnZ2dnUVZ8ternZ2d@bt.com>


"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?
>

  1. 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
  2. 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US