1 2 Previous Next 24 Replies Latest reply: Aug 16, 2011 6:12 AM by Jonathan Lewis RSS

    Local index:prefixed or non-prefixed

    573141
      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
          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
            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
              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
                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
                  >
                  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
                    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
                      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
                        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
                          hats off to Jonathan for all the details, simply amazing!

                          cheers
                          charles
                          • 10. Re: Local index:prefixed or non-prefixed
                            Mohamed Houri
                            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
                              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
                                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
                                  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
                                    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