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

From: vsevolod afanassiev <>
Date: Thu, 11 Aug 2011 16:37:42 -0700 (PDT)
Message-ID: <>

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. Received on Thu Aug 11 2011 - 18:37:42 CDT

Original text of this message