Re: Using Partitions With Date Range Question

ddf_dba_at_my-deja.com wrote in message <8iltor$6f$1_at_nnrp1.deja.com>...
>In article <961226216.10391.0.nnrp-10.9e984b29_at_news.demon.co.uk>,

>Notice I said "the local prifixed index". This implies one, not
>many, indexes. My statement is true since Oracle will scan all
>partitions for data if the local prefixed index is not used. One local
>prefixed index is all that is needed; I never stated otherwise. All
>other indexes can be local non-prefixed to allow other queries to
>utilize index scans rather than table scans to return data.
 

>

 . . . cut
>
>Precisely the reason for the local prefixed index. Utilization of this
>index should prevent scanning of unrelated partitions, thus eliminating
>the extraneous I/O such searches would require.
>

Apart from a very small number of special cases, a join to a partitioned table using a query that does not quote literal values for the partitioning columns will many redundant partitions. This general strategy behind behaviour will NOT be affected by prefixing, or not prefixing a local index.

The benefit of prefixed local indexes is that a join condition using the partitioning columns will have the smallest possible overhead because every attempt to join to an incorrect partition will fail very cheaply (with an average of just 3 or 4 logical I/Os per partition per parent row), whereas there is a high probability that many of the logical I/Os will become physical I/Os if the index is not prefixed.

Could you also clarify you comment above: are you suggesting above that there should be

    precisely one, or
    at most one
locally prefixed index ? Whichever your answer, I would have to disagree. Whilst it is often inappropriate to have two indexes on a single table which start with same column, the nature of Oracle's current optimisation strategies for partitioned tables requires you to ignore this rule of thumb from time to time.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk