Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

INDEX NOT USED IN A PARTITONED ORACLE TABLE

User_NROJDOct 1 2021

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?

Comments

Post Details

Added on Oct 1 2021
#general-database-discussions, #index
4 comments
377 views