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

From: vsevolod afanassiev <>
Date: Fri, 12 Aug 2011 17:51:53 -0700 (PDT)
Message-ID: <>

> Does the SQL used by the application include the YearMonth column in
> lookups ( in the WHERE clause )?

Yes, it does.

Let's say we have SQL statement SELECT * FROM ACCOUNTS WHERE YearMonth = :b1 and TransactionID = :b2.

Case#1: Non-unique local index on (YearMonth,TransactionID): Oracle will use RANGE SCAN on one partition.
Case#2: Non-unique local index on (TransactionID): As index is local and YearMonth is Partitioning Key Oracle will do partition pruning and then use RANGE SCAN on one partition.

So result is exactly the same. In fact Single-column index seems better as it will also perform range scan for statement where there is no condition on YearMonth: SELECT * FROM ACCOUNTS WHERE TransactionID = :b1.

Indexes need to be local as this is 24x7 system and we need to be able to drop partition without outage. Received on Fri Aug 12 2011 - 19:51:53 CDT

Original text of this message