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

From: Robert Klemme <>
Date: Sat, 13 Aug 2011 13:14:25 +0200
Message-ID: <>

On 13.08.2011 02:51, vsevolod afanassiev wrote:
>> 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.

Did you mean INDEX RANGE SCAN? Since YearMonth is in the index Oracle can do an INDEX RANGE SCAN (after partition pruning of course).

> 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.

Here Oracle can do pruning and then an INDEX RANGE SCAN. It must then look at all records found to determine whether YearMonth matches. Alternatively it might revert to a FULL SCAN of the partition (probably if it thinks the index range scan wasn't selective enough with regard to YearMonth values).

If the range partitioning ensures only one value of YearMonth per partition then in theory the INDEX RANGE SCAN would be be sufficient to determine all matching records. I don't know whether Oracle 9 does this.

> 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.

I don't think results are "exactly the same" as shown above. Did you look at real plans? What do they look like?

> Indexes need to be local as this is 24x7 system and we need to be able
> to drop partition without outage.

Btw, key compression will reduce the size overhead so the size difference between (YearMonth compressed, TransactionID) and (TransactionID) is probably not too big.

Kind regards


remember.guy do |as, often| as.you_can - without end
Received on Sat Aug 13 2011 - 06:14:25 CDT

Original text of this message