Re: Should one include partitioning key column as first column of non-unique local index
Date: Fri, 12 Aug 2011 09:10:49 -0700 (PDT)
Message-ID: <9a8f46b6-4e18-4051-8ed4-5bedc3c202d3_at_l9g2000prd.googlegroups.com>
On Aug 11, 4:37 pm, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> wrote:
> We have an application with range partitioned tables (Oracle 9.2.0.8).
> 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. http://forums.oracle.com/forums/thread.jspa?messageID=9696986#9696986 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)
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2011/aug/04/feds-raid-san-diego-vaccine-developer/Received on Fri Aug 12 2011 - 11:10:49 CDT