10 Replies Latest reply on Jun 1, 2015 11:43 PM by Jonathan Lewis

    Global partitioned index on range-partitioned table, but partition index does not work

    TzuWen, Lin

      Hi :

       

      I was create a partitioned index on partitioned table , but partitioned index does not work.

       

       

      create table table_range (

      CUST_FIRST_NAME VARCHAR2(20),

      CUST_GENDER CHAR(1),

      CUST_CITY VARCHAR2(30),

      COUNTRY_ISO_CODE CHAR(2),

      COUNTRY_NAME VARCHAR2(40),

      COUNTRY_SUBREGION VARCHAR2(30),

      PROD_ID NUMBER NOT NULL ,

      CUST_ID NUMBER  NOT NULL ,

      TIME_ID DATE  NOT NULL ,

      CHANNEL_ID    NUMBER  NOT NULL ,

      PROMO_ID NUMBER NOT NULL ,

      QUANTITY_SOLD NUMBER(10,2) NOT NULL ,

      AMOUNT_SOLD NUMBER(10,2) NOT NULL

      )

      partition by range (time_id) (

      partition p1 values less than (to_date('2001/01/01','YYYY/MM/DD')) tablespace u01,

      partition p2 values less than (to_date('2002/01/01','YYYY/MM/DD')) tablespace u02

      );

       

      create index ind_table_range on table2(prod_id)

      global partition by range(prod_id)(

      partition p1 values less than (100),

      partition p2 values less than (maxvalue)

      );

       

       

      SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_COUNT,HIGH_VALUE,NUM_ROWS from user_tab_partitions;

       

       

      TABLE_NAME  PARTITION_NAME   SUBPARTITION_COUNT HIGH_VALUE                                                                         NUM_ROWS

      ----------- ---------------- ------------------ -------------------------------------------------------------------------------- ----------

      TABLE_RANGE P2                                0 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     259418

      TABLE_RANGE P1                                0 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     659425

       

       

      SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from user_ind_partitions;

      INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE                 NUM_ROWS

      ------------------------------ ------------------------------ -------------------------- ----------

      IND_TABLE_RANGE                P1                             100                        479520

      IND_TABLE_RANGE                P2                             MAXVALUE                   439323

       

       

       

       

      SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_RANGE');

      SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_RANGE',GRANULARITY => 'PARTITION');

       

       

      SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE');

      SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE',GRANULARITY => 'PARTITION');

       

      SQL> set autotrace traceonly

      SQL> alter system flush shared_pool;

      SQL> alter system flush buffer_cache;

      SQL> select  * from table_range

      where prod_id = 127;

       

      ---------------------------------------------------------------------------------------------------

      | Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

      ---------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT    |             | 16469 |  1334K|  3579   (1)| 00:00:43 |       |       |

      |   1 |  PARTITION RANGE ALL|             | 16469 |  1334K|  3579   (1)| 00:00:43 |     1 |     2 |

      |*  2 |   TABLE ACCESS FULL | TABLE_RANGE | 16469 |  1334K|  3579   (1)| 00:00:43 |     1 |     2 |

      ---------------------------------------------------------------------------------------------------

       

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

         2 - filter("PROD_ID"=127)

       

      Statistics

      ----------------------------------------------------------

              320  recursive calls

                2  db block gets

            13352  consistent gets

            11820  physical reads

                0  redo size

           855198  bytes sent via SQL*Net to client

            12135  bytes received via SQL*Net from client

             1067  SQL*Net roundtrips to/from client

               61  sorts (memory)

                0  sorts (disk)

            15984  rows processed

        • 1. Re: Global partitioned index on range-partitioned table, not working
          Martin Preiss

          I guess it is working: it is just not used for the given query. If you add an index hint I am quite sure the index will be used.

           

          This may be a statistics problem: can you tell us something about the data distribution on prod_id? (i.e. number of distinct values, data skew)

          It could also be a problem of the clustering of data (i.e. the index access is indeed less effective than the FTS)

          • 2. Re: Global partitioned index on range-partitioned table, but partition index does not work
            TzuWen, Lin

            #####################  create nonpartitioned index , it is working

            SQL> drop index ind_table_range;

             

            Index dropped.

             

            SQL> create index ind_table_range on table_range(prod_id);

             

            Index created.

             

            SQL> execute dbms_stats.gather_index_stats(user,'ind_table_range');

             

            PL/SQL procedure successfully completed.

             

            SQL> alter system flush buffer_cache;

             

            System altered.

             

            SQL> select /*+INDEX(TABLE_RANGE IND_TABLE_RANGE)*/ * from table_range where prod_id = 127;

             

            15984 rows selected.

             

            Execution Plan

            ----------------------------------------------------------

            Plan hash value: 1902648947

             

            ----------------------------------------------------------------------------------------------------------------------

            | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

            ----------------------------------------------------------------------------------------------------------------------

            |   0 | SELECT STATEMENT                   |                 | 15560 |  1261K|   292   (1)| 00:00:04 |       |       |

            |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE_RANGE     | 15560 |  1261K|   292   (1)| 00:00:04 | ROWID | ROWID |

            |*  2 |   INDEX RANGE SCAN                 | IND_TABLE_RANGE | 15560 |       |    43   (0)| 00:00:01 |       |       |

            ----------------------------------------------------------------------------------------------------------------------

             

             

            #####################  Partitioned index is still not working

             

            SQL> drop index ind_table_range;

            SQL> create index ind_table_range on table2(prod_id)

            global partition by range(prod_id)(

            partition p1 values less than (20),

            partition p2 values less than (40),

            partition p3 values less than (60),

            partition p4 values less than (80),

            partition p5 values less than (100),

            partition p6 values less than (120),

            partition p7 values less than (140),

            partition p8 values less than (maxvalue)

            );

             

            Index created.

             

            SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE',GRANULARITY => 'PARTITION');

             

            PL/SQL procedure successfully completed.

             

            SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE');

             

            PL/SQL procedure successfully completed.

             

            SQL> select /*+INDEX(TABLE_RANGE IND_TABLE_RANGE)*/ * from table_range where prod_id = 127;

             

             

            15984 rows selected.

             

            Execution Plan

            ----------------------------------------------------------

            Plan hash value: 577948202

             

            ---------------------------------------------------------------------------------------------------

            | Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

            ---------------------------------------------------------------------------------------------------

            |   0 | SELECT STATEMENT    |             | 15560 |  1261K|  3579   (1)| 00:00:43 |       |       |

            |   1 |  PARTITION RANGE ALL|             | 15560 |  1261K|  3579   (1)| 00:00:43 |     1 |     2 |

            |*  2 |   TABLE ACCESS FULL | TABLE_RANGE | 15560 |  1261K|  3579   (1)| 00:00:43 |     1 |     2 |

            ---------------------------------------------------------------------------------------------------

             

            ########## number of distinct values, data skew

            -- the table "table_range" is create  from sh sample schema. (using sql loader)

             

            SQL>  select prod_id,count(prod_id)

            from table_range

            group by prod_id

            order by 1;

             

             

               PROD_ID COUNT(PROD_ID)

            ---------- --------------

                    13           6002

                    14           6010

                    15           5766

                    16           6929

                    17           6160

                    18           9591

                    19          10430

                    20          10903

                    21           5205

                    22           3441

                    23          19642

                    24          20948

                    25          19557

                    26          15950

                    27          12163

                    28          16796

                    29           7197

                    30          29282

                    31          23108

                    32          11253

                    33          22768

                    34          13043

                    35          16494

                    36          13008

                    37          17430

                    38           9523

                    39          13319

                    40          27114

                    41          12429

                    42          12116

                    43           8340

                    44           6113

                    45          10742

                    46          10156

                    47          12837

                    48          27755

                   113          15084

                   114          14315

                   115          10823

                   116          17389

                   117          17152

                   118          14381

                   119          22189

                   120          19403

                   121           9885

                   122           4019

                   123          13919

                   124          15305

                   125          15318

                   126          12292

                   127          15984

                   128          19233

                   129           7557

                   130          20490

                   131          15910

                   132          14183

                   133          16732

                   134           8990

                   135          11056

                   136            710

                   137          10225

                   138           5541

                   139          11574

                   140          14769

                   141           7844

                   142           6711

                   143           4572

                   144           4091

                   145           6168

                   146          12742

                   147           7576

                   148          15191

            • 3. Re: Global partitioned index on range-partitioned table, but partition index does not work
              John Stegeman

              In once sentence you say "not working" and then you proceed to paste plans that appear to show it "working"

               

              What gives?

               

              Actually, if you look at the plans - Oracle thinks you have 16k rows in the table and that it's going to return 12k rows for your select statement. In that case, Oracle is picking the right plan - full scanning 16k rows is much less work than scanning 12k index rows followed by 12k row lookups by rowid.

              • 4. Re: Global partitioned index on range-partitioned table, but partition index does not work
                TzuWen, Lin

                I made a mistake.

                The plan show it 'working' is nonpartition index. Partition index is still not working.

                 

                 

                #####################  create nonpartitioned index , it is working

                SQL> drop index ind_table_range;

                 

                Index dropped.

                 

                SQL> create index ind_table_range on table_range(prod_id);

                 

                Index created.

                 

                SQL> execute dbms_stats.gather_index_stats(user,'ind_table_range');

                 

                PL/SQL procedure successfully completed.

                 

                SQL> alter system flush buffer_cache;

                 

                System altered.

                 

                SQL> select /*+INDEX(TABLE_RANGE IND_TABLE_RANGE)*/ * from table_range where prod_id = 127;

                 

                15984 rows selected.

                 

                Execution Plan

                ----------------------------------------------------------

                Plan hash value: 1902648947

                 

                ----------------------------------------------------------------------------------------------------------------------

                | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                ----------------------------------------------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT                   |                 | 15560 |  1261K|   292   (1)| 00:00:04 |       |       |

                |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE_RANGE     | 15560 |  1261K|   292   (1)| 00:00:04 | ROWID | ROWID |

                |*  2 |   INDEX RANGE SCAN                 | IND_TABLE_RANGE | 15560 |       |    43   (0)| 00:00:01 |       |       |

                ----------------------------------------------------------------------------------------------------------------------

                 

                 

                #####################  Partitioned index is still not working

                 

                SQL> drop index ind_table_range;

                SQL> create index ind_table_range on table2(prod_id)

                global partition by range(prod_id)(

                partition p1 values less than (20),

                partition p2 values less than (40),

                partition p3 values less than (60),

                partition p4 values less than (80),

                partition p5 values less than (100),

                partition p6 values less than (120),

                partition p7 values less than (140),

                partition p8 values less than (maxvalue)

                );

                 

                Index created.

                 

                SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE',GRANULARITY => 'PARTITION');

                 

                PL/SQL procedure successfully completed.

                 

                SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE');

                 

                PL/SQL procedure successfully completed.

                 

                SQL> select /*+INDEX(TABLE_RANGE IND_TABLE_RANGE)*/ * from table_range where prod_id = 127;

                 

                 

                15984 rows selected.

                 

                Execution Plan

                ----------------------------------------------------------

                Plan hash value: 577948202

                 

                ---------------------------------------------------------------------------------------------------

                | Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                ---------------------------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT    |             | 15560 |  1261K|  3579   (1)| 00:00:43 |       |       |

                |   1 |  PARTITION RANGE ALL|             | 15560 |  1261K|  3579   (1)| 00:00:43 |     1 |     2 |

                |*  2 |   TABLE ACCESS FULL | TABLE_RANGE | 15560 |  1261K|  3579   (1)| 00:00:43 |     1 |     2 |

                ---------------------------------------------------------------------------------------------------

                 

                ########## number of distinct values, data skew

                -- the table "table_range" is create  from sh sample schema. (using sql loader)

                 

                SQL>  select prod_id,count(prod_id)

                from table_range

                group by prod_id

                order by 1;

                 

                 

                   PROD_ID COUNT(PROD_ID)

                ---------- --------------

                        13           6002

                        14           6010

                        15           5766

                        16           6929

                        17           6160

                        18           9591

                        19          10430

                        20          10903

                        21           5205

                        22           3441

                        23          19642

                        24          20948

                        25          19557

                        26          15950

                        27          12163

                        28          16796

                        29           7197

                        30          29282

                        31          23108

                        32          11253

                        33          22768

                        34          13043

                        35          16494

                        36          13008

                        37          17430

                        38           9523

                        39          13319

                        40          27114

                        41          12429

                        42          12116

                        43           8340

                        44           6113

                        45          10742

                        46          10156

                        47          12837

                        48          27755

                       113          15084

                       114          14315

                       115          10823

                       116          17389

                       117          17152

                       118          14381

                       119          22189

                       120          19403

                       121           9885

                       122           4019

                       123          13919

                       124          15305

                       125          15318

                       126          12292

                       127          15984

                       128          19233

                       129           7557

                       130          20490

                       131          15910

                       132          14183

                       133          16732

                       134           8990

                       135          11056

                       136            710

                       137          10225

                       138           5541

                       139          11574

                       140          14769

                       141           7844

                       142           6711

                       143           4572

                       144           4091

                       145           6168

                       146          12742

                       147           7576

                       148          15191

                • 5. Re: Global partitioned index on range-partitioned table, but partition index does not work
                  himmy

                  Agreed with John i have seen cases where developers complain the same thing

                  Oracle optimizer recognizes that the table is partitioned and goes straight for the partition depending on the where clause , now if the rows in partition are less their is no point going for index scan  overhead , instead it chooses fts.

                   

                  If the index on table is in valid state and query is running fine , then their should be no problem

                  • 6. Re: Re: Global partitioned index on range-partitioned table, but partition index does not work
                    Martin Preiss

                    without data it works for me as expected:

                    -- 12.1.0.1

                    drop table table_range;

                     

                    create table table_range (

                    CUST_FIRST_NAME VARCHAR2(20),

                    CUST_GENDER CHAR(1),

                    CUST_CITY VARCHAR2(30),

                    COUNTRY_ISO_CODE CHAR(2),

                    COUNTRY_NAME VARCHAR2(40),

                    COUNTRY_SUBREGION VARCHAR2(30),

                    PROD_ID NUMBER NOT NULL ,

                    CUST_ID NUMBER  NOT NULL ,

                    TIME_ID DATE  NOT NULL ,

                    CHANNEL_ID    NUMBER  NOT NULL ,

                    PROMO_ID NUMBER NOT NULL ,

                    QUANTITY_SOLD NUMBER(10,2) NOT NULL ,

                    AMOUNT_SOLD NUMBER(10,2) NOT NULL

                    )

                    partition by range (time_id) (

                    partition p1 values less than (to_date('2001/01/01','YYYY/MM/DD')) ,

                    partition p2 values less than (to_date('2002/01/01','YYYY/MM/DD'))

                    );

                     

                    create index ind_table_range on table_range(prod_id)

                    global partition by range(prod_id)(

                    partition p1 values less than (20),

                    partition p2 values less than (40),

                    partition p3 values less than (60),

                    partition p4 values less than (80),

                    partition p5 values less than (100),

                    partition p6 values less than (120),

                    partition p7 values less than (140),

                    partition p8 values less than (maxvalue)

                    );

                     

                    EXECUTE DBMS_STATS.GATHER_INDEX_STATS(USER,'IND_TABLE_RANGE');

                     

                    explain plan for

                    select /*+INDEX(TABLE_RANGE IND_TABLE_RANGE)*/ * from table_range where prod_id = 127;

                     

                    -------------------------------------------------------------------------------------------------------------------------------

                    | Id  | Operation                                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                    -------------------------------------------------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT                            |                 |     1 |   162 |     1   (0)| 00:00:01 |       |       |

                    |   1 |  PARTITION RANGE SINGLE                     |                 |     1 |   162 |     1   (0)| 00:00:01 |     7 |     7 |

                    |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TABLE_RANGE     |     1 |   162 |     1   (0)| 00:00:01 | ROWID | ROWID |

                    |*  3 |    INDEX RANGE SCAN                         | IND_TABLE_RANGE |     1 |       |     1   (0)| 00:00:01 |     7 |     7 |

                    -------------------------------------------------------------------------------------------------------------------------------

                     

                    Predicate Information (identified by operation id):

                    ---------------------------------------------------

                       3 - access("PROD_ID"=127)

                     

                    Note

                    -----

                       - dynamic statistics used: dynamic sampling (level=2)

                    With a correct index hint I would expect the optimizer to follow the directive.

                    • 7. Re: Global partitioned index on range-partitioned table, but partition index does not work
                      TzuWen, Lin

                      Just like Martin says.  without data it works.

                       

                      thanks for help.

                      • 8. Re: Global partitioned index on range-partitioned table, but partition index does not work
                        Jonathan Lewis

                        From your sample code:

                          SQL> create index ind_table_range on table2(prod_id);

                         

                        The index doesn't seem to be created on the table you are querying.

                        Was this a copying error, or have you been indexing another copy of the data by mistake ?

                         

                        The cost of 292 for the global index seems remarkably low - the 16,000 rows have to be packed at about 55 per block to get that cost - is that really what the data looks like ?

                        There should be no reason for the optimizer to bypass the index (especially if hinted) when you partition it unless you've hit a bug - which version are you using ?

                         

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Global partitioned index on range-partitioned table, but partition index does not work
                          Jonathan Lewis

                          The plans show 15560 or 16469 rows (depending wheterh you look at the older or newer plans) selected from the table, and the reported stats tell us that there are about 920,000 rows in the table. I can't see where you got the 12,000 from.

                           

                          Whatever the numbers, there is a theoretical flaw in your argument - when the index is global it is used with a cost of 292,. when it is globally partitioned it is not used and a tablescan with a cost of 3579 is used EVEN when the index is hinted.  Since the hint appears to be valid it should work so, unless my observation about it being created on the wrong table is correct, there is a problem with the optimizer recognizing the suitability of the index.  If the index is correct but the hint is (somehow) invalid it means the cost of using the index is too high - which it shouldn't be unless there's an anomaly in the optimizer arithmetic somewhere.

                           

                          The cost of the indexed access when the index is global is 292 - so the data MUST be well ordered by product_id in the table, even if there are two well-ordered areas, one in each table partition to allow the clustering factor to be small enough for the cost to be so low..  Since the index is partitioned by product id all the index entries for product_id 127 will be in one partiiton - which means the clustering_factor of that index partition ought to be virtually identical to the clustering_factor of the global index - either both indexes should be used, or bith indexes should be rejected.

                           

                          Obviously we may be seeing an optimizer bug - for example, (wild hypothesis alert) maybe the optimizer_index_cost_adj is set to a low value and the optimizer is applying it when calculating the cost of the global index but "forgetting" to apply it when calculating the cost of the globally partitioned index.

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Global partitioned index on range-partitioned table, but partition index does not work
                            Jonathan Lewis

                            Have you worked out why your testing of the globally partitioned index is not working correctly ?

                             

                            Regards

                            Jonathan Lewis