Forum Stats

  • 3,728,018 Users
  • 2,245,519 Discussions
  • 7,853,245 Comments

Discussions

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

TzuWen, Lin
TzuWen, Lin Member Posts: 14

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

himmyMartin Preissrchemkumarsk

Best Answer

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2015 Accepted Answer

    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.

    himmy

Answers

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited May 2015

    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)

  • TzuWen, Lin
    TzuWen, Lin Member Posts: 14
    edited May 2015

    #####################  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

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2015 Accepted Answer

    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.

    himmy
  • TzuWen, Lin
    TzuWen, Lin Member Posts: 14
    edited May 2015

    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

  • himmy
    himmy Member Posts: 322
    edited May 2015

    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

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited May 2015

    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.

  • TzuWen, Lin
    TzuWen, Lin Member Posts: 14
    edited May 2015

    Just like Martin says.  without data it works.

    thanks for help.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited May 2015

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited May 2015

    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

    Martin Preissrchemkumarsk
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited June 2015

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

    Regards

    Jonathan Lewis

This discussion has been closed.