This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Aug 16, 2011 4:12 AM by Jonathan Lewis RSS

Local index:prefixed or non-prefixed

573141 Newbie
Currently Being Moderated
hi,

i understand that, in order to effectively use the local index, WHERE clause of the sql should have the partition column
as a predicate.

generally, partition column will not be a highly selective column specific to the individual partition(even at the table level).

in which situations,  prefixed local index (where partition column will be the leading index) will provide benefits?
in my case, i have list partitioned table with 2 partitions, 1) current 2) obsolete.

thanks,
charles
  • 1. Re: Local index:prefixed or non-prefixed
    UweHesse Expert
    Currently Being Moderated
    Why do you partition your table in the first place? 2 partitions seem to me a very low number. With only 2 partitions, it will probably not matter much whether you have local prefixed indexes or local nonprefixed indexes, performancewise.

    Generally speaking, a local prefixed index enables partition pruning on the index level for statements that list the index key in ther where clause. A nonprefixed local index doesn't.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 2. Re: Local index:prefixed or non-prefixed
    573141 Newbie
    Currently Being Moderated
    hi Uwe,
    in the partitioned table, one partition is for the active records and other for the inactive/read only records as per functional requirements.

    i didnt understand
    Uwe Hesse wrote:
    Generally speaking, a local prefixed index enables partition pruning on the index level for statements that list the index key in ther where clause. A nonprefixed local index doesn't.
    my understanding is,
    since the local indexes are aligned with the individual partitions, if the WHERE clause has the partition column as a predicate, partition pruning
    will be achieved.

    then, why there is the requirement to include the partition column in the index as a prefixed column?

    in some forums, it is mentioned that the prefixed local index was introduced to eliminate some of the parition issues associated with older versions.


    thanks,
    charles
  • 3. Re: Local index:prefixed or non-prefixed
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Uwe Hesse wrote:

    Generally speaking, a local prefixed index enables partition pruning on the index level for statements that list the index key in ther where clause. A nonprefixed local index doesn't.
    Uwe,

    Can you supply a demonstration to show exactly what you mean. I believe that that argument ceased to be true in 8.1.6 or 8.1.7 when the optimizer code caught up with the fact that you could do partition pruning at the index level even if the partitioning column wasn't part of the (local) index. For example:
    create table pt_range (
         id,
         grp,
         small_vc,
         padding
    )
    partition by range(grp) (
         partition p25 values less than (25),
         partition p50 values less than (50)
    )
    as
    select
         rownum               id,
         mod(rownum,50)          grp,
         lpad(rownum,10)          small_vc,
         rpad('x',100)          padding
    from
         all_objects
    where 
         rownum <= 500
    ;
    
    create index ptr_i1 on pt_range(id) local;
    
    execute dbms_stats.gather_table_stats(user,'pt_range')
    
    spool temp
    
    select
         *
    from
         pt_range
    where
         id  = 480
    and     grp = 30
    ;
    
    set autotrace off
    The execution plan for this query uses partition pruning - even though the grp column isn't in the index:
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |          |     1 |   118 |     2   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE            |          |     1 |   118 |     2   (0)| 00:00:01 |     2 |     2 |
    |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PT_RANGE |     1 |   118 |     2   (0)| 00:00:01 |     2 |     2 |
    |*  3 |    INDEX RANGE SCAN                | PTR_I1   |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("GRP"=30)
       3 - access("ID"=480)
    Regards
    Jonathan Lewis
  • 4. Re: Local index:prefixed or non-prefixed
    UweHesse Expert
    Currently Being Moderated
    Jonathan,

    probably my point is just too obvious and self-evident. Example:
    create table t (id, number, a number, b number) 
    partition by list (a)
    (
    partion p1 values ('A'),
    partition p2 values ('B')
    );
    
    create index ipref on t (a) local;
    create index inonpref on t (b) local;
    Now the folllowing select will only need to search one index partition;
    select * from t where a='A';
    While the following select will need to search all (only 2 in that example) index partitions;
    select * from t where b='Z';
    I rely that you don't need to see the execution plans for that claim :-)

    Your point is that the following select would only need to search one index partition (even if index ipref wouldn't exist):
    select * from t where b='Z' and a='A';
    In short: If you can manage to include the partition key of the table to your where condition, partition pruning on the index level for local indexes is possible. But that may not always be possible.

    Therefore, if you have a local nonprefixed index on a table with a high number of partitions, selects that cannot include the partition key will need to process through all the index partitions, which may be especially undesireable for OLTP Databases where the work is done with one server process for each session.

    You knew that of course, but that was my (obvious) point :-)

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 5. Re: Local index:prefixed or non-prefixed
    UweHesse Expert
    Currently Being Moderated
    >
    my understanding is,
    since the local indexes are aligned with the individual partitions, if the WHERE clause has the partition column as a predicate, partition pruning
    will be achieved.
    >

    Your understanding is correct. But I said

    >
    A local prefixed index enables partition pruning on the index level for statements that list the index key in the where clause. A nonprefixed local index doesn't.
    >

    index key is not partition key (of the table) for an nonprefixed local index.

    Hope that I made my point clear with the answer I gave to Jonathan.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 6. Re: Local index:prefixed or non-prefixed
    UweHesse Expert
    Currently Being Moderated
    Just discovered that the coded example wouldn't work because of the wrong datatype number. But you will get the idea of it in spite, I hope :-)
  • 7. Re: Local index:prefixed or non-prefixed
    573141 Newbie
    Currently Being Moderated
    hi Uwe,
    Uwe Hesse wrote:

    In short: If you can manage to include the partition key of the table to your where condition, partition pruning on the index level for local indexes is possible. But that may not always be possible.

    Therefore, if you have a local nonprefixed index on a table with a high number of partitions, selects that cannot include the partition key will need to process through all the index partitions, which may be especially undesireable for OLTP Databases where the work is done with one server process for each session.
    what i uderstand is,

    as per your example, let us create the composite index
    create index ipref on t (a,b) local;
    even if with a prefixed local index, if the WHERE clause doesn't have the prefixed column(here, partitioning column) partitioning pruning will not work ,needs to scan all partitions
    select * from t where b='Z';
    then, what is the meaning in having a prefixed local index?

    thanks,
    charles
  • 8. Re: Local index:prefixed or non-prefixed
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Uwe Hesse wrote:
    Uwe,
    In short: If you can manage to include the partition key of the table to your where condition, partition pruning on the index level for local indexes is possible. But that may not always be possible.
    But if the where clause doesn't contain the partition key then you have to visit every partition of the index (and, possibly, table) anyway - and the index can't be used for a high-precision range scans because it starts with a column that's not in the where clause (you might get lucky with skip scans, though).


    There are four conditions to consider once you've decided on a possible local index and think you're supposed to decide whether or not the index should be prefixed:

    Where clause includes partition key, index is not prefixed (even to the extent that the partition key is not part of the index)
    Partition elimination can take place

    Where clause includes partition key, index is prefixed
    Partition elimination can take place

    Where clause does not include partition key, index is not prefixed (even to the extent that the partition key is not part of the index)
    Partition elimination cannot take place - but the index might still be very precise and highly effective

    Where clause does not include partition key, index is prefixed
    Partition elimination cannot take place, and the index can only be used through a skip scan or full scan because it starts with a column that is not in the where clause.

    The first pair of options show that there is no inherent benefit to prefixing, the second pair show that there is a potential threat.

    Since something like 8.1.6 the only considerations for including the partitioning key in any local indexes are the standard ones - by how much does it improve precision, where - in the index column order - is it's most useful position. The whole prefix/non-prefix concept should have been buried years ago.


    Regards
    Jonathan Lewis
  • 9. Re: Local index:prefixed or non-prefixed
    573141 Newbie
    Currently Being Moderated
    hats off to Jonathan for all the details, simply amazing!

    cheers
    charles
  • 10. Re: Local index:prefixed or non-prefixed
    Mohamed Houri Pro
    Currently Being Moderated
    This is just to give example of what you said above
    mhouri> create index nonprefixed_ind on pt_range(id) local;
    
    mhouri> select
      2      *
      3  from
      4      pt_range
      5  where
      6      id  = 480
      7  and grp = 30  -- partition key is involved in the where clause
      8  ;
    
      ID        GRP SMALL_VC   PADDING                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    --------- ---------- ---------- ------------               
        480         30        480 x           
    
    mhouri> select *
      2  from table(dbms_xplan.display_cursor);
                                                  
    ----------------------------------------------------------------------------------------------------------------------       
    | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                                                                                                                                                                                                              
    ----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                              
    |   0 | SELECT STATEMENT                   |                 |       |       |     2 (100)|          |       |       |  
    |   1 |  PARTITION RANGE SINGLE            |                 |     1 |   118 |     2   (0)| 00:00:01 |     2 |     2 |   
    |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PT_RANGE        |     1 |   118 |     2   (0)| 00:00:01 |     2 |     2 | 
    |*  3 |    INDEX RANGE SCAN                | NONPREFIXED_IND |     1 |       |     1   (0)| 00:00:01 |     2 |     2 | 
    ---------------------------------------------------------------------------------------------------------------------- 
                                                                                                                                                                                                                                                                               Predicate Information (identified by operation id):    
    ---------------------------------------------------                                                                                                                                                                                    
                                    
       2 - filter("GRP"=30)            
       3 - access("ID"=480)         
    Look how(through Pstart 2 and Pstop 2) partition elimination occured on the NONPREFIXED_IND because of the presence of the partition key in the query where clause
    mhouri> select
      2      *
      3  from
      4      pt_range
      5  where
      6      id  = 480
      7  --and grp = 30  exclude the partition key from the where
      8  ;
    
            ID        GRP SMALL_VC   PADDING                      
    ---------- ---------- ---------- --------------------------   
           480         30        480 x          
    
    mhouri> select *
      2  from table(dbms_xplan.display_cursor);       
    ----------------------------------------------------------------------------------------------------------------------     
    | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------   
    |   0 | SELECT STATEMENT                   |                 |       |       |     2 (100)|          |       |       |   
    |   1 |  PARTITION RANGE ALL               |                 |     1 |   118 |     2   (0)| 00:00:01 |     1 |     2 |   
    |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PT_RANGE        |     1 |   118 |     2   (0)| 00:00:01 |     1 |     2 |  
    |*  3 |    INDEX RANGE SCAN                | NONPREFIXED_IND |     1 |       |     1   (0)| 00:00:01 |     1 |     2 | 
    ----------------------------------------------------------------------------------------------------------------------  
                                        
    Predicate Information (identified by operation id):  
    --------------------------------------------------- 
       3 - access("ID"=480)      
    And from above, how(through Pstart 1 and Pstop 2) the optimiser was not able to remove partition p25(Pstart 1) because of the absence of the partition key from the query where clause.

    But in this particular case where I want to cover both queries I had better to use a nonprefixed index of the following form *"create index nonprefixed_ind on pt_range(id, grp) local;"*
    Best Regards
    Mohamed Houri
  • 11. Re: Local index:prefixed or non-prefixed
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    The whole prefix/non-prefix concept should have been buried years ago.
    Except where the LOCAL index is to be a UNIQUE Index. The Partition key has to be a subset of the Index columns. In that case, I might as well the Index with the Partition Key -- it becomes "compress"ible.


    Hemant K Chitale
  • 12. Re: Local index:prefixed or non-prefixed
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hemant K Chitale wrote:
    The whole prefix/non-prefix concept should have been buried years ago.
    Except where the LOCAL index is to be a UNIQUE Index. The Partition key has to be a subset of the Index columns. In that case, I might as well the Index with the Partition Key -- it becomes "compress"ible.
    Assume you have a unique key which is multi-column and one of the key columns, which has a small number of distinct values, is also the partitioning key - this seems to be the scenario you are describing. Are you planning to put the partitioning column as the first column of the index BECAUSE it is the partition key or are you thinking about putting it there because, all other things being equal, having a highly repetitive column at the leading edge may give you better compressibility of the index ?

    It ought to be the latter - i.e. "prefixing" is a redundant concept; you're doing what you would do with any b-tree index whether or not the table or the index were partitioned.

    The only time that "prefixing" comes close to being a respectable concept is when you look at globally range-partitioned indexes where the partitioning key MUST be the leading edge of the index - and even then it's redundant terminology because you don't have an option to choose between "prefixed" and "non-prefixed".


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    Edited by: Jonathan Lewis on Feb 26, 2011 11:42 PM
    Corrected a couple of spelling errors after following new link
  • 13. Re: Local index:prefixed or non-prefixed
    635471 Expert
    Currently Being Moderated
    Hemant K Chitale wrote:
    The whole prefix/non-prefix concept should have been buried years ago.
    Except where the LOCAL index is to be a UNIQUE Index. The Partition key has to be a subset of the Index columns. In that case, I might as well the Index with the Partition Key -- it becomes "compress"ible.


    Hemant K Chitale
    In theory I suppose that if Oracle could enforce that a table's partitioning scheme allowed only one value of the partition key per column, which is a fairly common situation and matches the OP's scenario, then for this type of table that restriction could be lifted. In fact, the partition column could be entirely a logical entity and wouldn't have to exist physically at all.

    All sorts of interesting optimisations could be possible, some of which are theoretically possible with list partitioning at the moment but I doubt that they're implemented.

    For example if a table is partitioned so that only the value 'A' can exist in a particular partition then one could query:
    select partition_key_column, other_column, count(*)
    from partitioned_table
    where partition_key_column = 'A'
    group by partition_key_column, other_column
    ... and the optimizer could use a scan of a local non-prefixed index on (other_column) to supply the result.
  • 14. Re: Local index:prefixed or non-prefixed
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    because, all other things being equal, having a highly repetitive column at the leading edge may give you better compressibility of the index
    Yes, for 'compress"ibility.

    I was posting with reference to the issue whether the Partition Key should ever be part of the Index --- that is a requirement for a Unique Local Index.


    Hemant K Chitale
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points