Re: Should one include partitioning key column as first column of non-unique local index
Date: Fri, 12 Aug 2011 17:51:53 -0700 (PDT)
Message-ID: <5da5b9df-25b8-4d71-ab35-ef6b1b077c77_at_l9g2000prd.googlegroups.com>
> 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