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

From: John Hurley <>
Date: Fri, 12 Aug 2011 17:13:42 -0700 (PDT)
Message-ID: <>

On Aug 11, 7: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.

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

Original text of this message