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

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.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#4602

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/