Re: Local index in partitioned table and columns order

> In particular, is it necessary keep columns, on which I partitoned a table, leading?
I think you are talking about "Local prefixed indexes" vs. "Local nonprefixed indexes" here. This depends on how your queries are built. There is a common myth about "Local nonprefixed indexes", that they do not support partition pruning/elimination, but this is not true. They support partition pruning/elimination, but do not enforce it. However if you need the local index to support a unqiue or primary key constraint, the partition key needs to be included, but it does not matter if local prefixed or nonprefixed.

> Then, is it better keep leading columns more or less selective?
Really depends on the queries.

I think you should check chapter 13 (starting on page 621) of Tom Kyte's book "Expert Oracle Database Architecture" (http://www.apress.com/9781430262985). The concept, restrictions and designs are explained very well.  

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Sandro Gallo <sandro.gallo.81_at_gmail.com> hat am 15. Mai 2015 um 19:24 geschrieben:
>
> Hi all,
> My question is about what order gives to columns while defining a local index.
>
> In particular, is it necessary keep columns, on which I partitoned a table, leading?
> Then, is it better keep leading columns more or less selective?
>
> I explain me with my real case.
> I have composite partitioned table:
> interval on date column and hash on other columns ( that contain account information, my client is a bank ).
> Columns of hash are high selective, date column distribute data in an uniform way.
> In my local primary key there are other fields with a low selectivity.
>
> I work on Exadata machine, 11G.
> On Exadata is it necessary to make different considerations?
>
> Thanks
> Best regards
> Sandro

--
http://www.freelists.org/webpage/oracle-l