Forum Stats

  • 3,770,163 Users
  • 2,253,079 Discussions
  • 7,875,352 Comments

Discussions

INDEX NOT USED IN A PARTITONED ORACLE TABLE

I have a table partitioned by list

create table ICT_ENCOURS_BRUT
(
  code_exercice           VARCHAR2(4),
  CODE_PRODUIT            VARCHAR2(2),
  .......
)
partition by list (CODE_EXERCICE)
(
  partition CODE_EXERCICE_2018 values ('2018')
    tablespace DATA_BI_PARTITION_2018
  ,
  partition CODE_EXERCICE_2019 values ('2019')
    tablespace DATA_BI_PARTITION_2019
    ,
  partition CODE_EXERCICE_2020 values ('2020')
    tablespace DATA_BI_PARTITION_2020
   ,
  partition CODE_EXERCICE_2021 values ('2021')
    tablespace DATA_BI_PARTITION_2021
    
);

and i have 2 indexes :

CREATE INDEX EKIPIC.IDX_CODE_EXERCICE ON EKIPIC.ICT_ENCOURS_BRUT
(CODE_EXERCICE)
  TABLESPACE INDEX_BI
  
LOGGING
LOCAL (  
  PARTITION CODE_EXERCICE_2018
    NOCOMPRESS 
    TABLESPACE INDEX_BI_PARTITION_2018,  
  PARTITION CODE_EXERCICE_2019
    NOCOMPRESS 
    TABLESPACE INDEX_BI_PARTITION_2019,  
  PARTITION CODE_EXERCICE_2020
    NOCOMPRESS 
    TABLESPACE INDEX_BI_PARTITION_2020,  
  PARTITION CODE_EXERCICE_2021
    NOCOMPRESS 
    TABLESPACE INDEX_BI_PARTITION_2021
)

ONLINE;

create index IDX_CODE_PRODUIT on ICT_ENCOURS_BRUT (CODE_PRODUIT)
  tablespace INDEX_BI
 ;

the explain plan show a full scan access when i perform

 SELECT * from ICT_ENCOURS_BRUT
where code_produit='LOA'

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows   | Bytes     | Cost  | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  | 812259 | 242053182 | 32268 | 00:07:32 |
|   1 |   PARTITION LIST ALL |                  | 812259 | 242053182 | 32268 | 00:07:32 |
| * 2 |    TABLE ACCESS FULL | ICT_ENCOURS_BRUT | 812259 | 242053182 | 32268 | 00:07:32 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("CODE_PRODUIT"='LOA')

the same way when i try to use

select * from ICT_ENCOURS_BRUT i where i.code_exercice='2019'

 Plan Hash Value  : 2304474508 

--------------------------------------------------------------------------------------------
| Id | Operation               | Name             | Rows    | Bytes     | Cost  | Time     |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |                  | 1604226 | 474850896 | 12511 | 00:02:56 |
|  1 |   PARTITION LIST SINGLE |                  | 1604226 | 474850896 | 12511 | 00:02:56 |
|  2 |    TABLE ACCESS FULL    | ICT_ENCOURS_BRUT | 1604226 | 474850896 | 12511 | 00:02:56 |
--------------------------------------------------------------------------------------------

Why cannot use the indexes assuming that stats is updated?

Tagged:

Answers

  • JohnWatson2
    JohnWatson2 Member Posts: 4,328 Silver Crown
    edited Oct 1, 2021 4:48PM

    Your index is useless for that query, because it is no more selective than partition pruning. If you force Oracle to use it with a hint, you will likely find that performance degrades because indexed access paths use single block reads whereas scan access paths can do multiblock reads.

    User_NROJD
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond

    You got the answer to your second query. In regards to your first query. We don't know your data. We don't know what percentage of table rows have CODE_PRODUIT='LOA'. Optimizer based on stats decides what is more expensive full scan or index range scan.

    SY.

  • User_GSQTY
    User_GSQTY Member Posts: 55 Bronze Badge

    Very likely it's related to your data.

    Here is what I got as execution plan in my test database after creating your table, indexes and insert 1 row into it


    [email protected]> select * from ICT_ENCOURS_BRUT i where i.code_exercice='2019' ;
    no rows selected
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 561929496
    
    -------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation                 | Name       | Rows | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT              |         |   1 |   7 |   1  (0)| 00:00:01 |    |    |
    |  1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ICT_ENCOURS_BRUT |   1 |   7 |   1  (0)| 00:00:01 |   2 |   2 |
    |* 2 |  INDEX RANGE SCAN             | IDX_CODE_EXERCIE |   1 |    |   1  (0)| 00:00:01 |    |    |
    -------------------------------------------------------------------------------------------------------------------------------
    


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Oct 1, 2021 6:38PM

    Actually index was used due to no data and no stats. As soon as you populate it and collect stats optimizer realizes all rows within 2019 partition are selected so using index will be way more costly:

     set autotrace off
    SQL> DROP TABLE ICT_ENCOURS_BRUT PURGE;
    
    Table dropped.
    
    SQL> create table ICT_ENCOURS_BRUT
      2  (
      3    code_exercice           VARCHAR2(4),
      4    CODE_PRODUIT            VARCHAR2(2)
      5  )
      6  partition by list (CODE_EXERCICE)
      7  (
      8    partition CODE_EXERCICE_2018 values ('2018')
      9    ,
     10    partition CODE_EXERCICE_2019 values ('2019')
     11      ,
     12    partition CODE_EXERCICE_2020 values ('2020')
     13     ,
     14    partition CODE_EXERCICE_2021 values ('2021')
     15  );
    
    Table created.
    
    SQL> CREATE INDEX IDX_CODE_EXERCICE ON ICT_ENCOURS_BRUT
      2  (CODE_EXERCICE)
      3  LOGGING
      4  LOCAL (
      5    PARTITION CODE_EXERCICE_2018
      6      NOCOMPRESS,
      7    PARTITION CODE_EXERCICE_2019
      8      NOCOMPRESS,
      9    PARTITION CODE_EXERCICE_2020
     10      NOCOMPRESS,
     11    PARTITION CODE_EXERCICE_2021
     12      NOCOMPRESS
     13  )
     14  ONLINE;
    
    Index created.
    
    SQL> create index IDX_CODE_PRODUIT on ICT_ENCOURS_BRUT (CODE_PRODUIT);
    
    Index created.
    
    SQL> set autotrace traceonly
    SQL> select * from ICT_ENCOURS_BRUT i where i.code_exercice='2019' ;
    
    no rows selected
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 164955771
    
    --------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                  | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                           |                   |     1 |     7 |     1   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION LIST SINGLE                     |                   |     1 |     7 |     1   (0)| 00:00:01 |     2 |     2 |
    |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ICT_ENCOURS_BRUT  |     1 |     7 |     1   (0)| 00:00:01 |     2 |     2 |
    |*  3 |    INDEX RANGE SCAN                        | IDX_CODE_EXERCICE |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
    --------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("I"."CODE_EXERCICE"='2019')
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    Statistics
    ----------------------------------------------------------
             22  recursive calls
              0  db block gets
             23  consistent gets
              0  physical reads
              0  redo size
            423  bytes sent via SQL*Net to client
            596  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              0  rows processed
    
    SQL> set autotrace off
    SQL> INSERT INTO ICT_ENCOURS_BRUT SELECT '2019','P' FROM DUAL CONNECT BY LEVEL <= 100000;
    
    100000 rows created.
    
    SQL> exec dbms_stats.gather_table_stats('SCOTT','ICT_ENCOURS_BRUT')
    
    PL/SQL procedure successfully completed.
    
    SQL> set autotrace traceonly
    SQL> select * from ICT_ENCOURS_BRUT i where i.code_exercice='2019';
    
    100000 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3557510171
    
    ----------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                  | 99991 |   683K|   275   (1)| 00:00:01 |       |       |
    |   1 |  PARTITION LIST SINGLE|                  | 99991 |   683K|   275   (1)| 00:00:01 |     2 |     2 |
    |   2 |   TABLE ACCESS FULL   | ICT_ENCOURS_BRUT | 99991 |   683K|   275   (1)| 00:00:01 |     2 |     2 |
    ----------------------------------------------------------------------------------------------------------
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           6880  consistent gets
              0  physical reads
              0  redo size
        1773879  bytes sent via SQL*Net to client
          73933  bytes received via SQL*Net from client
           6668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         100000  rows processed
    
    SQL>
    
    
    

    SY.

    User_NROJD