Re: Should one include partitioning key column as first column of non-unique local index
From: John Hurley <hurleyjohnb_at_yahoo.com>
Date: Fri, 12 Aug 2011 17:13:42 -0700 (PDT)
Message-ID: <495ff436-4f6c-4c87-9498-4b0bbbf460e9_at_f8g2000yqb.googlegroups.com>
On Aug 11, 7: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.
Date: Fri, 12 Aug 2011 17:13:42 -0700 (PDT)
Message-ID: <495ff436-4f6c-4c87-9498-4b0bbbf460e9_at_f8g2000yqb.googlegroups.com>
On Aug 11, 7: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.
Does the SQL used by the application include the YearMonth column in lookups ( in the WHERE clause )? Received on Fri Aug 12 2011 - 19:13:42 CDT