Re: Should one include partitioning key column as first column of non-unique local index

From: joel garry <>
Date: Fri, 12 Aug 2011 09:10:49 -0700 (PDT)
Message-ID: <>

On Aug 11, 4:37 pm, vsevolod afanassiev
<> wrote:
> We have an application with range partitioned tables (Oracle
> All partitioned tables use the same partitioning key column called
> YearMonth in YYYYMM format (type NUMBER). All indexes on partitioned
> tables are partitioned local.
> I know that partitioning key column needs to be included in unique
> indexes.
> However application vendor also included YearMonth column in many
> non-unique indexes, so we have two- and three-column indexes
> (YearMonth,AccountID), (YearMonth,TransactionID,TransactionStatus),
> etc.
> I suspect that including partitioning key column in non-unique indexes
> isn't required, it just makes index bigger without any benefit.
> We keep 2 years of data, so there are 24 partitions and 24 values of
> YearMonth,
> while there are millions of Accounts and Transactions.

Maybe I'm not understanding something, but wouldn't there be a benefit of allowing index skip scans when you look at accounts across months? I guess the way to not guess would be to try it with and without the key column and see if you get different amounts of partition probing.

I have a vague memory of 9i having bugs with some skip scans. I also have a vague memory of your question being discussed somewhere, but heck if I can find it quickly. has thoughts on evaluating such things.

(Actually, I found a quote from Jonathan, but seem to be having trouble with google advanced search to link to it: "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." Subject: Re: Question About Indexes on a Partitioned Table in this group 06-23-2007)


-- is bogus.
Received on Fri Aug 12 2011 - 11:10:49 CDT

Original text of this message