Skip to Main Content
  • Questions

  • Primary Key Index not getting picked on Partitioned Table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Anshul.

Asked: March 24, 2022 - 12:51 pm UTC

Last updated: June 07, 2022 - 3:13 am UTC

Version: 19.0.0.0.0

Viewed 1000+ times

You Asked

We have below table...

boards(account_id number, b_key varchar2(36),status varchar2(2), ....)
.

This table is range partitioned on account_id. There is composite primary key (account_id, b_key) on this table.

When we are executing query on this table like below...

SELECT b_key, status,.....
FROM boards
WHERE account_id = ? and status = 'N';


Above query is not picking up the Primary key index and going for FTS. Records are around 20 millions in this table and one there can be multiple records for one account_id with different b_key's.

What could be the potential issue here and how can we resolve this.

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

Actual Query:
---------------

SELECT /*+ gather_plan_statistics */ board_key
       ,board_name
       ,created_date
       ,last_updated_date
       ,start_date
       ,end_date
       ,object_key
       ,object_name
       ,size_in_bytes
       ,mime_type
       ,mime_sub_type
       ,orientation
       ,cover_pic_object_key
       ,cover_pic_mime_type
       ,is_jaws
       ,storage_id
       ,'Y' AS cover_pic_is_jaws
       ,source_folder
       ,NULL AS available_suggestions
       ,width
       ,height
       ,cover_pic_storage_id
       ,cover_pic_width
       ,cover_pic_height
       ,cover_pic_orientation
FROM (SELECT  a.board_key
               ,a.board_name
               ,a.created_date
               ,a.last_updated_date
               ,a.start_date
               ,a.end_date
               ,c.file_created_date
               ,c.object_key
               ,c.object_name
               ,c.size_in_bytes
               ,c.mime_type
               ,c.mime_sub_type
               ,e.orientation
               ,a.cover_pic_object_key
               ,a.cover_pic_mime_type
               ,NVL(e.is_jaws, 'N') is_jaws
               ,(CASE WHEN oudm.device_id = NULL THEN source_folder ELSE NULL END) source_folder
               ,e.storage_id
               ,e.width
               ,e.height
               ,cl.width       AS cover_pic_width
               ,cl.height      AS cover_pic_height
               ,cl.orientation AS cover_pic_orientation
               ,(SELECT storage_id
                 FROM physical_files p
                 WHERE p.physical_file_id = cl.physical_file_id
                ) AS cover_pic_storage_id
    FROM  (SELECT board_key
                   ,board_name  
                   ,created_date
                   ,last_updated_date
                   ,start_date
                   ,end_date
                   ,account_id
                   ,cover_pic_object_key
                   ,cover_pic_mime_type
             FROM (SELECT /*+index(suggested_boards,PK_SUGGESTED_BOARDS)*/ DENSE_RANK () OVER (PARTITION BY account_id ORDER BY sgst_board_start_date DESC ) brdNo
                        ,sgst_board_key AS board_key
                        ,sgst_board_name AS board_name
                        ,created_date
                        ,last_updated_date
                        ,sgst_board_start_date AS start_date
                        ,sgst_board_end_date AS end_date
                        ,account_id
                        ,cover_pic_object_key
                        ,cover_pic_mime_type
                  FROM suggested_boards
                  WHERE account_id = 19199940
                    AND sgst_board_status = 'N'
                  )
             WHERE brdNo <= 15
            ) a
           ,suggested_board_objects b
           ,object_metadata c
           ,physical_files e
           ,object_user_device_mapping oudm
           ,object_metadata cl
    WHERE a.account_id = b.account_id
        AND a.board_key = b.sgst_board_key
        AND b.account_id = c.account_id 
        AND b.object_key = c.object_key
        AND c.status = 'A'
        AND c.physical_file_id = e.physical_file_id
        AND c.object_id = oudm.object_id
        AND a.account_id           = cl.account_id(+)
        AND a.cover_pic_object_key = cl.object_key(+)
   )
ORDER BY start_date DESC, file_created_date DESC;

Explain Plan without Index Hint:
------------------------------------

SQL_ID  4a0zj4q2anndm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ board_key        ,board_name       
 ,created_date        ,last_updated_date        ,start_date        
,end_date        ,object_key        ,object_name        ,size_in_bytes  
      ,mime_type        ,mime_sub_type        ,orientation        
,cover_pic_object_key        ,cover_pic_mime_type        ,is_jaws       
 ,storage_id        ,:"SYS_B_0" AS cover_pic_is_jaws        
,source_folder        ,NULL AS available_suggestions        ,width      
  ,height        ,cover_pic_storage_id        ,cover_pic_width        
,cover_pic_height        ,cover_pic_orientation FROM (SELECT  
a.board_key                ,a.board_name                ,a.created_date 
               ,a.last_updated_date                ,a.start_date        
        ,a.end_date                ,c.file_created_date                
,c.object_key                ,c.object_name                
,c.size_in_bytes                ,c.mime_type                
,c.mime_sub_type                ,e.orientation
 
Plan hash value: 2423163536
 
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |      1 |        |    250 |00:00:00.01 |    3132 |       |       |          |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID      | PHYSICAL_FILES             |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN                      | PHYSICAL_FILE_MASTER_PK    |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   3 |  SORT ORDER BY                           |                            |      1 |   5524 |    250 |00:00:00.01 |    3132 | 80896 | 80896 |71680  (0)|
|   4 |   NESTED LOOPS                           |                            |      1 |   5524 |    250 |00:00:00.02 |    3132 |       |       |          |
|   5 |    NESTED LOOPS                          |                            |      1 |   5552 |    250 |00:00:00.02 |    2899 |       |       |          |
|   6 |     NESTED LOOPS                         |                            |      1 |   5552 |    250 |00:00:00.01 |    2130 |       |       |          |
|   7 |      NESTED LOOPS                        |                            |      1 |   5629 |    250 |00:00:00.01 |    1127 |       |       |          |
|   8 |       NESTED LOOPS OUTER                 |                            |      1 |   5629 |    255 |00:00:00.01 |     105 |       |       |          |
|   9 |        NESTED LOOPS                      |                            |      1 |   5629 |    255 |00:00:00.01 |     105 |       |       |          |
|* 10 |         VIEW                             |                            |      1 |    413 |     15 |00:00:00.01 |      68 |       |       |          |
|* 11 |          WINDOW SORT PUSHED RANK         |                            |      1 |    413 |     15 |00:00:00.01 |      68 | 74752 | 74752 |65536  (0)|
|  12 |           PARTITION RANGE SINGLE         |                            |      1 |    413 |    428 |00:00:00.04 |      68 |       |       |          |
|* 13 |            TABLE ACCESS FULL             | SUGGESTED_BOARDS           |      1 |    413 |    428 |00:00:00.04 |      68 |       |       |          |
|  14 |         PARTITION RANGE ITERATOR         |                            |     15 |     14 |    255 |00:00:00.01 |      37 |       |       |          |
|* 15 |          INDEX RANGE SCAN                | PK_SUGGESTED_BOARD_OBJECTS |     15 |     14 |    255 |00:00:00.01 |      37 |       |       |          |
|* 16 |        TABLE ACCESS BY GLOBAL INDEX ROWID| OBJECT_METADATA            |    255 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 17 |         INDEX UNIQUE SCAN                | UQ_OM                      |    255 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 18 |       TABLE ACCESS BY GLOBAL INDEX ROWID | OBJECT_METADATA            |    255 |      1 |    250 |00:00:00.01 |    1022 |       |       |          |
|* 19 |        INDEX UNIQUE SCAN                 | UQ_OM                      |    255 |      1 |    255 |00:00:00.01 |     767 |       |       |          |
|  20 |      TABLE ACCESS BY GLOBAL INDEX ROWID  | PHYSICAL_FILES             |    250 |      1 |    250 |00:00:00.01 |    1003 |       |       |          |
|* 21 |       INDEX UNIQUE SCAN                  | PHYSICAL_FILE_MASTER_PK    |    250 |      1 |    250 |00:00:00.01 |     752 |       |       |          |
|* 22 |     INDEX RANGE SCAN                     | IDX_OUDM_OBJUSRDEVID       |    250 |      1 |    250 |00:00:00.01 |     769 |       |       |          |
|  23 |    TABLE ACCESS BY GLOBAL INDEX ROWID    | OBJECT_USER_DEVICE_MAPPING |    250 |      1 |    250 |00:00:00.01 |     233 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("P"."PHYSICAL_FILE_ID"=:B1)
  10 - filter("BRDNO"<=:SYS_B_4)
  11 - filter(DENSE_RANK() OVER ( PARTITION BY "ACCOUNT_ID" ORDER BY INTERNAL_FUNCTION("SGST_BOARD_START_DATE") DESC )<=:SYS_B_4)
  13 - filter(("ACCOUNT_ID"=:SYS_B_2 AND "SGST_BOARD_STATUS"=:SYS_B_3))
  15 - access("ACCOUNT_ID"="B"."ACCOUNT_ID" AND "BOARD_KEY"="B"."SGST_BOARD_KEY")
  16 - filter("ACCOUNT_ID"="CL"."ACCOUNT_ID")
  17 - access("COVER_PIC_OBJECT_KEY"="CL"."OBJECT_KEY")
  18 - filter(("B"."ACCOUNT_ID"="C"."ACCOUNT_ID" AND "C"."STATUS"=:SYS_B_5))
  19 - access("B"."OBJECT_KEY"="C"."OBJECT_KEY")
  21 - access("C"."PHYSICAL_FILE_ID"="E"."PHYSICAL_FILE_ID")
  22 - access("C"."OBJECT_ID"="OUDM"."OBJECT_ID")
 


Explain Plan with Index Hint:
------------------------------------

SQL_ID  16nqyt795m9a4, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ board_key        ,board_name       
 ,created_date        ,last_updated_date        ,start_date        
,end_date        ,object_key        ,object_name        ,size_in_bytes  
      ,mime_type        ,mime_sub_type        ,orientation        
,cover_pic_object_key        ,cover_pic_mime_type        ,is_jaws       
 ,storage_id        ,:"SYS_B_0" AS cover_pic_is_jaws        
,source_folder        ,NULL AS available_suggestions        ,width      
  ,height        ,cover_pic_storage_id        ,cover_pic_width        
,cover_pic_height        ,cover_pic_orientation FROM (SELECT  
a.board_key                ,a.board_name                ,a.created_date 
               ,a.last_updated_date                ,a.start_date        
        ,a.end_date                ,c.file_created_date                
,c.object_key                ,c.object_name                
,c.size_in_bytes                ,c.mime_type                
,c.mime_sub_type                ,e.orientation
 
Plan hash value: 3770519488
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                    |                            |      1 |        |    250 |00:00:00.01 |    3391 |       |       |          |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID                 | PHYSICAL_FILES             |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  2 |   INDEX UNIQUE SCAN                                 | PHYSICAL_FILE_MASTER_PK    |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   3 |  SORT ORDER BY                                      |                            |      1 |   5524 |    250 |00:00:00.01 |    3391 | 80896 | 80896 |71680  (0)|
|   4 |   NESTED LOOPS                                      |                            |      1 |   5524 |    250 |00:00:00.01 |    3391 |       |       |          |
|   5 |    NESTED LOOPS                                     |                            |      1 |   5552 |    250 |00:00:00.01 |    3158 |       |       |          |
|   6 |     NESTED LOOPS                                    |                            |      1 |   5552 |    250 |00:00:00.01 |    2389 |       |       |          |
|   7 |      NESTED LOOPS                                   |                            |      1 |   5629 |    250 |00:00:00.01 |    1386 |       |       |          |
|   8 |       NESTED LOOPS OUTER                            |                            |      1 |   5629 |    255 |00:00:00.01 |     364 |       |       |          |
|   9 |        NESTED LOOPS                                 |                            |      1 |   5629 |    255 |00:00:00.01 |     364 |       |       |          |
|* 10 |         VIEW                                        |                            |      1 |    413 |     15 |00:00:00.01 |     327 |       |       |          |
|* 11 |          WINDOW SORT PUSHED RANK                    |                            |      1 |    413 |     15 |00:00:00.01 |     327 | 86016 | 86016 |75776  (0)|
|  12 |           PARTITION RANGE SINGLE                    |                            |      1 |    413 |    428 |00:00:00.01 |     327 |       |       |          |
|* 13 |            TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SUGGESTED_BOARDS           |      1 |    413 |    428 |00:00:00.01 |     327 |       |       |          |
|* 14 |             INDEX RANGE SCAN                        | PK_SUGGESTED_BOARDS        |      1 |    414 |    429 |00:00:00.01 |       5 |       |       |          |
|  15 |         PARTITION RANGE ITERATOR                    |                            |     15 |     14 |    255 |00:00:00.01 |      37 |       |       |          |
|* 16 |          INDEX RANGE SCAN                           | PK_SUGGESTED_BOARD_OBJECTS |     15 |     14 |    255 |00:00:00.01 |      37 |       |       |          |
|* 17 |        TABLE ACCESS BY GLOBAL INDEX ROWID           | OBJECT_METADATA            |    255 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 18 |         INDEX UNIQUE SCAN                           | UQ_OM                      |    255 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 19 |       TABLE ACCESS BY GLOBAL INDEX ROWID            | OBJECT_METADATA            |    255 |      1 |    250 |00:00:00.01 |    1022 |       |       |          |
|* 20 |        INDEX UNIQUE SCAN                            | UQ_OM                      |    255 |      1 |    255 |00:00:00.01 |     767 |       |       |          |
|  21 |      TABLE ACCESS BY GLOBAL INDEX ROWID             | PHYSICAL_FILES             |    250 |      1 |    250 |00:00:00.01 |    1003 |       |       |          |
|* 22 |       INDEX UNIQUE SCAN                             | PHYSICAL_FILE_MASTER_PK    |    250 |      1 |    250 |00:00:00.01 |     752 |       |       |          |
|* 23 |     INDEX RANGE SCAN                                | IDX_OUDM_OBJUSRDEVID       |    250 |      1 |    250 |00:00:00.01 |     769 |       |       |          |
|  24 |    TABLE ACCESS BY GLOBAL INDEX ROWID               | OBJECT_USER_DEVICE_MAPPING |    250 |      1 |    250 |00:00:00.01 |     233 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("P"."PHYSICAL_FILE_ID"=:B1)
  10 - filter("BRDNO"<=:SYS_B_4)
  11 - filter(DENSE_RANK() OVER ( PARTITION BY "ACCOUNT_ID" ORDER BY INTERNAL_FUNCTION("SGST_BOARD_START_DATE") DESC )<=:SYS_B_4)
  13 - filter("SGST_BOARD_STATUS"=:SYS_B_3)
  14 - access("ACCOUNT_ID"=:SYS_B_2)
  16 - access("ACCOUNT_ID"="B"."ACCOUNT_ID" AND "BOARD_KEY"="B"."SGST_BOARD_KEY")
  17 - filter("ACCOUNT_ID"="CL"."ACCOUNT_ID")
  18 - access("COVER_PIC_OBJECT_KEY"="CL"."OBJECT_KEY")
  19 - filter(("B"."ACCOUNT_ID"="C"."ACCOUNT_ID" AND "C"."STATUS"=:SYS_B_5))
  20 - access("B"."OBJECT_KEY"="C"."OBJECT_KEY")
  22 - access("C"."PHYSICAL_FILE_ID"="E"."PHYSICAL_FILE_ID")
  23 - access("C"."OBJECT_ID"="OUDM"."OBJECT_ID")

and Connor said...

Initial Response
============
Can you run the following and paste back in the question

SELECT /*+ gather_plan_statistics */ b_key, status,.....
FROM boards
WHERE account_id = ? and status = 'N';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

and then the same with an index hint to force the index use

SELECT /*+ gather_plan_statistics index(boards (account_id)) */ b_key, status,.....
FROM boards
WHERE account_id = ? and status = 'N';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


=============

Thanks your patience.

Looking at the two plans, you can see it was almost a "tie" in terms of how the optimizer predicted which was better.

|  12 |           PARTITION RANGE SINGLE         |                            |      1 |    413 |    428 |00:00:00.04 |      68 |       |       |          |
|* 13 |            TABLE ACCESS FULL             | SUGGESTED_BOARDS           |      1 |    413 |    428 |00:00:00.04 |      68 |       |       |          |


|  12 |           PARTITION RANGE SINGLE                    |                            |      1 |    413 |    428 |00:00:00.01 |     327 |       |       |          |
|* 13 |            TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SUGGESTED_BOARDS           |      1 |    413 |    428 |00:00:00.01 |     327 |       |       |          |
|* 14 |             INDEX RANGE SCAN                        | PK_SUGGESTED_BOARDS        |      1 |    414 |    429 |00:00:00.01 |       5 |       |       |          |


Notice that even though this is a primary key, you're only using *part* of it, hence its a range scan. That will impact the costing. From the plans, we can see the optimizer thought the full table scan is expected to be a little quicker with less buffers, but the true time shows the index was indeed quicker. A potential cause here is that the database is over-estimating the cost of an index read. It might be worth checking the clustering factor on this index, and if necessary, adjusting it was TABLE_CACHED_BLOCKS.

Here's an excellent post on that topic and technique

https://richardfoote.wordpress.com/2020/06/30/oracle-19c-automatic-indexing-common-index-creation-trap-rat-trap/


Rating

  (7 ratings)

Comments

Add Cost to plan output

Narendra, April 28, 2022 - 8:17 am UTC

I guess adding COST to the plan would be a good indicator of whether optimizer is assigning lower cost to one plan over another.
Running display_cursor as below should produce the plan with cost.

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));

Connor McDonald
May 03, 2022 - 2:41 am UTC

nice input

Primary Key local index is not picked up in the plan

A reader, May 24, 2022 - 9:41 pm UTC

Greetings!

Good plan (Preprod) using Primary Key Local partitioned index in the plan.

Bad plan (Dev) doing for full table scan on the partitions.

Data volume is the same. Table/indexes/structure are identical.
Re-gathered stats as well as copied stats from good env to slow environment but no difference in the plan.

If i force the Primary Key index in the query, then oracle is using the index and plan is as expected.

Any suggestions on further troubleshooting this.

SQL> select owner,table_name, num_rows, blocks from dba_tables where table_name='F_MKT_PRC' ;

OWNER                TABLE_NAME                             NUM_ROWS     BLOCKS
-------------------- ------------------------------ ---------------- ----------
MHIST                F_MKT_PRC                         7,275,003,909  142692804


SQL> select owner, index_name, auto, constraint_index, visibility, status, clustering_factor from dba_indexes where table_name='F_MKT_PRC' ;

OWNER                INDEX_NAME                     AUT CON VISIBILIT STATUS   CLUSTERING_FACTOR
-------------------- ------------------------------ --- --- --------- -------- -----------------
MHIST                F_MKT_PRC_PK                   NO  NO  VISIBLE   N/A             4959337853

Connor McDonald
May 25, 2022 - 3:47 am UTC

Run each (the default query and the hinted query) and show us this after each:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));

Primary Key Index not getting picked on Partitioned Table

A reader, May 25, 2022 - 3:50 pm UTC

Hi Connor,

Please find below the execution plan of the query with and without INDEX HINT. Thanks.

1) query execution as is:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  azadabvh1qdk7, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ '"' || ds.SOURCE_MNEMONIC || '",'
                       || DECODE(ds.RTS_SOURCE,null,'"!NA"',ds.RTS_SOURC
E) || ','                         || DECODE(F_MKT_P.CURRENCY,null,'"!NA"',
'"' || F_MKT_P.CURRENCY ||'"'),
DECODE(F_MKT_P.LAST_TRADE_PRICE,null,'"!NA"',F_MKT_P.LAST_TRADE_PRICE) || ','
                      || DECODE(F_MKT_P.LAST_TRADE_PRICE_TYPE,null,'"!NA"',f
mp.LAST_TRADE_PRICE_TYPE)                         ||
',"!NA","!NA","!NA","!NA","!NA",'                         ||
DECODE(F_MKT_P.LAST_BID_PRICE,null,'"!NA"',F_MKT_P.LAST_BID_PRICE) || ',"!NA"',
                        F_MKT_P.PRC_DT,
DECODE(F_MKT_P.COUNTRY,null,'"!NA"','"' || F_MKT_P.COUNTRY || '"'),
ds.SOURCE_TYPE                 FROM MHIST.F_MKT_PRC F_MKT_P
          INNER JOIN (                         SELECT MM.MARKET_ID AS
the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID = 12621381
                      UNION SELECT MM.MARKET_ID AS the_id FROM
IMSG.MKT_MAST MM WH

Plan hash value: 1312552425

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |       1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         |      1 |        | 20204 (100)|      2 |00:00:07.71 |    1452K|    845 |       |            |          |
|   1 |  SORT ORDER BY                             |                         |      1 |     25 | 20204  (15)|      2 |00:00:07.71 |    1452K|    845 |  2048 |       2048 | 2048  (0)|
|*  2 |   HASH JOIN                                |                         |      1 |     25 | 20203  (15)|      2 |00:00:07.71 |    1452K|    845 |  2981K|       2650K| 5204K (0)|
|   3 |    JOIN FILTER CREATE                      | :BF0000                 |      1 |     39 | 20148  (15)|  11679 |00:00:06.49 |    1452K|    845 |       |            |          |
|*  4 |     HASH JOIN                              |                         |      1 |     39 | 20148  (15)|  11679 |00:00:06.48 |    1452K|    845 |  2673K|       2673K| 4335K (0)|
|   5 |      JOIN FILTER CREATE                    | :BF0001                 |      1 |     78 |     2   (0)|   1137 |00:00:00.01 |      21 |      0 |       |            |          |
|*  6 |       TABLE ACCESS STORAGE FULL            | D_SOURCE                |      1 |     78 |     2   (0)|   1137 |00:00:00.01 |      21 |      0 |  1025K|       1025K|          |
|   7 |      JOIN FILTER USE                       | :BF0001                 |      1 |    281 | 20146  (15)|  11679 |00:00:05.48 |    1452K|    845 |       |            |          |
|   8 |       PARTITION RANGE SINGLE               |                         |      1 |    281 | 20146  (15)|  11679 |00:00:05.48 |    1452K|    845 |       |            |          |
|   9 |        PARTITION HASH ALL                  |                         |      1 |    281 | 20146  (15)|  11679 |00:00:05.48 |    1452K|    845 |       |            |          |
|* 10 |         TABLE ACCESS STORAGE FULL          | F_MKT_PRC               |     64 |    281 | 20146  (15)|  11679 |00:00:05.91 |    1452K|    845 |  1025K|       1025K|          |
|  11 |    VIEW                                    |                         |      1 |    180 |    55  (13)|      2 |00:00:00.01 |      59 |      0 |       |            |          |
|  12 |     SORT UNIQUE                            |                         |      1 |    180 |    55  (13)|      2 |00:00:00.01 |      59 |      0 |  2048 |       2048 | 2048  (0)|
|  13 |      UNION-ALL                             |                         |      1 |        |            |      4 |00:00:00.01 |      59 |      0 |       |            |          |
|  14 |       JOIN FILTER USE                      | :BF0000                 |      1 |      2 |     5   (0)|      2 |00:00:00.01 |       7 |      0 |       |            |          |
|  15 |        TABLE ACCESS BY INDEX ROWID BATCHED | MKT_MAST                |      1 |      2 |     5   (0)|      3 |00:00:00.01 |       7 |      0 |       |            |          |
|* 16 |         INDEX RANGE SCAN                   | MKT_MAST_IDX2           |      1 |      2 |     4   (0)|      3 |00:00:00.01 |       4 |      0 |  1025K|       1025K|          |
|  17 |       JOIN FILTER USE                      | :BF0000                 |      1 |     85 |    10   (0)|      0 |00:00:00.01 |       3 |      0 |       |            |          |
|  18 |        NESTED LOOPS                        |                         |      1 |     85 |    10   (0)|      0 |00:00:00.01 |       3 |      0 |       |            |          |
|  19 |         NESTED LOOPS                       |                         |      1 |     85 |    10   (0)|      0 |00:00:00.01 |       3 |      0 |       |            |          |
|* 20 |          INDEX RANGE SCAN                  | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       3 |      0 |  1025K|       1025K|          |
|* 21 |          INDEX RANGE SCAN                  | MKT_MAST_IDX2           |      0 |      2 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |  1025K|       1025K|          |
|  22 |         TABLE ACCESS BY INDEX ROWID        | MKT_MAST                |      0 |     56 |     5   (0)|      0 |00:00:00.01 |       0 |      0 |       |            |          |
|  23 |       JOIN FILTER USE                      | :BF0000                 |      1 |      2 |     6   (0)|      0 |00:00:00.01 |       8 |      0 |       |            |          |
|  24 |        NESTED LOOPS                        |                         |      1 |      2 |     6   (0)|      0 |00:00:00.01 |       8 |      0 |       |            |          |
|  25 |         TABLE ACCESS BY INDEX ROWID        | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |      0 |       |            |          |
|* 26 |          INDEX UNIQUE SCAN                 | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |  1025K|       1025K|          |
|  27 |         TABLE ACCESS BY INDEX ROWID BATCHED| MKT_MAST                |      1 |      2 |     5   (0)|      0 |00:00:00.01 |       4 |      0 |       |            |          |
|* 28 |          INDEX RANGE SCAN                  | MKT_MAST_IDX2           |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       4 |      0 |  1025K|       1025K|          |
|  29 |       JOIN FILTER USE                      | :BF0000                 |      1 |     85 |    13   (0)|      2 |00:00:00.01 |      27 |      0 |       |            |          |
|  30 |        NESTED LOOPS                        |                         |      1 |     85 |    13   (0)|      3 |00:00:00.01 |      27 |      0 |       |            |          |
|  31 |         NESTED LOOPS                       |                         |      1 |     85 |    13   (0)|      3 |00:00:00.01 |      24 |      0 |       |            |          |
|* 32 |          INDEX RANGE SCAN                  | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      5 |00:00:00.01 |       7 |      0 |  1025K|       1025K|          |
|  33 |           TABLE ACCESS BY INDEX ROWID      | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |      0 |       |            |          |
|* 34 |            INDEX UNIQUE SCAN               | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |  1025K|       1025K|          |
|* 35 |          INDEX RANGE SCAN                  | MKT_MAST_IDX2           |      5 |      2 |     3   (0)|      3 |00:00:00.01 |      17 |      0 |  1025K|       1025K|          |
|  36 |         TABLE ACCESS BY INDEX ROWID        | MKT_MAST                |      3 |     56 |     5   (0)|      3 |00:00:00.01 |       3 |      0 |       |            |          |
|  37 |       JOIN FILTER USE                      | :BF0000                 |      1 |        |            |      0 |00:00:00.01 |       0 |      0 |       |            |          |
|  38 |        FAST DUAL                           |                         |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       0 |      0 |       |            |          |
|  39 |       JOIN FILTER USE                      | :BF0000                 |      1 |      1 |     2   (0)|      0 |00:00:00.01 |       4 |      0 |       |            |          |
|  40 |        TABLE ACCESS BY INDEX ROWID         | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |      0 |       |            |          |
|* 41 |         INDEX UNIQUE SCAN                  | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |  1025K|       1025K|          |
|  42 |       JOIN FILTER USE                      | :BF0000                 |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       7 |      0 |       |            |          |
|* 43 |        INDEX RANGE SCAN                    | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      5 |00:00:00.01 |       7 |      0 |  1025K|       1025K|          |
|  44 |         TABLE ACCESS BY INDEX ROWID        | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |      0 |       |            |          |
|* 45 |          INDEX UNIQUE SCAN                 | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      0 |  1025K|       1025K|          |
|  46 |       JOIN FILTER USE                      | :BF0000                 |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       3 |      0 |       |            |          |
|* 47 |        INDEX RANGE SCAN                    | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       3 |      0 |  1025K|       1025K|          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("F_MKT_P"."LST_ID"="DT"."THE_ID")
   4 - access("DS"."SRC_ID"="F_MKT_P"."SRC_ID")
   6 - storage(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("DS"."RECORD_DT_END"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
       filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("DS"."RECORD_DT_END"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
  10 - storage(("F_MKT_P"."PRC_DT"<=TO_DATE(' 2008-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F_MKT_P"."PRC_DT">=TO_DATE(' 2008-02-05 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND (SYS_EXTRACT_UTC(INTERNAL_FUNCTION("F_MKT_P"."RECORD_DT_END"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) OR "F_MKT_P"."RECORD_DT_END" IS NULL) AND CASE  WHEN
              ((-200)=(-100) AND "F_MKT_P"."COUNTRY"='US') THEN 1 WHEN ((-200)=(-200) AND "F_MKT_P"."COUNTRY"='CA') THEN 1 WHEN ((-200)=(-300) AND ("F_MKT_P"."COUNTRY"='CA' OR
              "F_MKT_P"."COUNTRY"='US')) THEN 1 WHEN ((-200)=(-500) AND ("F_MKT_P"."COUNTRY"='CA' OR "F_MKT_P"."COUNTRY"='US')) THEN 1 ELSE 0 END =1 AND
              SYS_OP_BLOOM_FILTER(:BF0001,"F_MKT_P"."SRC_ID")))
       filter(("F_MKT_P"."PRC_DT"<=TO_DATE(' 2008-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F_MKT_P"."PRC_DT">=TO_DATE(' 2008-02-05 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND (SYS_EXTRACT_UTC(INTERNAL_FUNCTION("F_MKT_P"."RECORD_DT_END"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) OR "F_MKT_P"."RECORD_DT_END" IS NULL) AND CASE  WHEN
              ((-200)=(-100) AND "F_MKT_P"."COUNTRY"='US') THEN 1 WHEN ((-200)=(-200) AND "F_MKT_P"."COUNTRY"='CA') THEN 1 WHEN ((-200)=(-300) AND ("F_MKT_P"."COUNTRY"='CA' OR
              "F_MKT_P"."COUNTRY"='US')) THEN 1 WHEN ((-200)=(-500) AND ("F_MKT_P"."COUNTRY"='CA' OR "F_MKT_P"."COUNTRY"='US')) THEN 1 ELSE 0 END =1 AND
              SYS_OP_BLOOM_FILTER(:BF0001,"F_MKT_P"."SRC_ID")))
  16 - access("MM"."INSTR_ID"=12621381)
  20 - access("L"."ORIGINAL_ID"=12621381)
  21 - access("MM"."INSTR_ID"="L"."INSTR_ID")
       filter("MM"."INSTR_ID" IS NOT NULL)
  26 - access("INSTR_ID"=12621381)
  28 - access("MM"."INSTR_ID"="ORIGINAL_ID")
       filter("MM"."INSTR_ID" IS NOT NULL)
  32 - access("L"."ORIGINAL_ID"=)
       filter("L"."ORIGINAL_ID"<>0)
  34 - access("INSTR_ID"=12621381)
  35 - access("MM"."INSTR_ID"="L"."INSTR_ID")
       filter("MM"."INSTR_ID" IS NOT NULL)
  41 - access("INSTR_ID"=12621381)
  43 - access("L"."ORIGINAL_ID"=)
       filter("L"."ORIGINAL_ID"<>0)
  45 - access("INSTR_ID"=12621381)
  47 - access("L"."ORIGINAL_ID"=12621381)


109 rows selected.


2) Query execution with Local Primary Key Index Hint

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b37ps3wyxb7y9, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(F_MKT_P F_MKT_PRC_PK) */
'"' || ds.SOURCE_MNEMONIC || '",'                         ||
DECODE(ds.RTS_SOURCE,null,'"!NA"',ds.RTS_SOURCE) || ','
        || DECODE(F_MKT_P.CURRENCY,null,'"!NA"', '"' || F_MKT_P.CURRENCY
||'"'),                         DECODE(F_MKT_P.LAST_TRADE_PRICE,null,'"!NA"'
,F_MKT_P.LAST_TRADE_PRICE) || ','                         ||
DECODE(F_MKT_P.LAST_TRADE_PRICE_TYPE,null,'"!NA"',F_MKT_P.LAST_TRADE_PRICE_TYPE)
                         || ',"!NA","!NA","!NA","!NA","!NA",'
              || DECODE(F_MKT_P.LAST_BID_PRICE,null,'"!NA"',F_MKT_P.LAST_BID_PRI
CE) || ',"!NA"',                         F_MKT_P.PRC_DT,
DECODE(F_MKT_P.COUNTRY,null,'"!NA"','"' || F_MKT_P.COUNTRY || '"'),
ds.SOURCE_TYPE                 FROM MHIST.F_MKT_PRC F_MKT_P
          INNER JOIN (                         SELECT MM.MARKET_ID AS
the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID = 12621381
                      UNION SELECT MM.MARKET_ID AS the_id

Plan hash value: 3367685684

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                         |      1 |        | 23284 (100)|      2 |00:00:00.01 |     114 |       |       |          |
|   1 |  SORT ORDER BY                              |                         |      1 |     25 | 23284   (1)|      2 |00:00:00.01 |     114 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN                                 |                         |      1 |     25 | 23283   (1)|      2 |00:00:00.01 |     114 |  2673K|  2673K| 5342K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL                | D_SOURCE                |      1 |     78 |     2   (0)|   1137 |00:00:00.01 |      21 |  1025K|  1025K|          |
|   4 |    NESTED LOOPS                             |                         |      1 |    180 | 23281   (1)|      2 |00:00:00.01 |      93 |       |       |          |
|   5 |     NESTED LOOPS                            |                         |      1 |    180 | 23281   (1)|      4 |00:00:00.01 |      89 |       |       |          |
|   6 |      VIEW                                   |                         |      1 |    180 |    55  (13)|      9 |00:00:00.01 |      59 |       |       |          |
|   7 |       SORT UNIQUE                           |                         |      1 |    180 |    55  (13)|      9 |00:00:00.01 |      59 |  2048 |  2048 | 2048  (0)|
|   8 |        UNION-ALL                            |                         |      1 |        |            |     13 |00:00:00.01 |      59 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID BATCHED | MKT_MAST                |      1 |      2 |     5   (0)|      3 |00:00:00.01 |       7 |       |       |          |
|* 10 |          INDEX RANGE SCAN                   | MKT_MAST_IDX2           |      1 |      2 |     4   (0)|      3 |00:00:00.01 |       4 |  1025K|  1025K|          |
|  11 |         NESTED LOOPS                        |                         |      1 |     85 |    10   (0)|      0 |00:00:00.01 |       3 |       |       |          |
|  12 |          NESTED LOOPS                       |                         |      1 |     85 |    10   (0)|      0 |00:00:00.01 |       3 |       |       |          |
|* 13 |           INDEX RANGE SCAN                  | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       3 |  1025K|  1025K|          |
|* 14 |           INDEX RANGE SCAN                  | MKT_MAST_IDX2           |      0 |      2 |     3   (0)|      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
|  15 |          TABLE ACCESS BY INDEX ROWID        | MKT_MAST                |      0 |     56 |     5   (0)|      0 |00:00:00.01 |       0 |       |       |          |
|  16 |         NESTED LOOPS                        |                         |      1 |      2 |     6   (0)|      0 |00:00:00.01 |       8 |       |       |          |
|  17 |          TABLE ACCESS BY INDEX ROWID        | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |       |       |          |
|* 18 |           INDEX UNIQUE SCAN                 | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|  19 |          TABLE ACCESS BY INDEX ROWID BATCHED| MKT_MAST                |      1 |      2 |     5   (0)|      0 |00:00:00.01 |       4 |       |       |          |
|* 20 |           INDEX RANGE SCAN                  | MKT_MAST_IDX2           |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       4 |  1025K|  1025K|          |
|  21 |         NESTED LOOPS                        |                         |      1 |     85 |    13   (0)|      3 |00:00:00.01 |      27 |       |       |          |
|  22 |          NESTED LOOPS                       |                         |      1 |     85 |    13   (0)|      3 |00:00:00.01 |      24 |       |       |          |
|* 23 |           INDEX RANGE SCAN                  | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      5 |00:00:00.01 |       7 |  1025K|  1025K|          |
|  24 |            TABLE ACCESS BY INDEX ROWID      | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |       |       |          |
|* 25 |             INDEX UNIQUE SCAN               | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|* 26 |           INDEX RANGE SCAN                  | MKT_MAST_IDX2           |      5 |      2 |     3   (0)|      3 |00:00:00.01 |      17 |  1025K|  1025K|          |
|  27 |          TABLE ACCESS BY INDEX ROWID        | MKT_MAST                |      3 |     56 |     5   (0)|      3 |00:00:00.01 |       3 |       |       |          |
|  28 |         FAST DUAL                           |                         |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       0 |       |       |          |
|  29 |         TABLE ACCESS BY INDEX ROWID         | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |       |       |          |
|* 30 |          INDEX UNIQUE SCAN                  | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|* 31 |         INDEX RANGE SCAN                    | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      5 |00:00:00.01 |       7 |  1025K|  1025K|          |
|  32 |          TABLE ACCESS BY INDEX ROWID        | INSTR_LNKGE             |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |       |       |          |
|* 33 |           INDEX UNIQUE SCAN                 | INSTR_LNKGE_PK          |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|* 34 |         INDEX RANGE SCAN                    | INSTR_LNKGE_IDX1        |      1 |      2 |     3   (0)|      0 |00:00:00.01 |       3 |  1025K|  1025K|          |
|  35 |      PARTITION RANGE SINGLE                 |                         |      9 |      1 |   128   (0)|      4 |00:00:00.01 |      30 |       |       |          |
|  36 |       PARTITION HASH ITERATOR               |                         |      9 |      1 |   128   (0)|      4 |00:00:00.01 |      30 |       |       |          |
|* 37 |        INDEX RANGE SCAN                     | F_MKT_PRC_PK            |      9 |      1 |   128   (0)|      4 |00:00:00.01 |      30 |  1025K|  1025K|          |
|* 38 |     TABLE ACCESS BY LOCAL INDEX ROWID       | F_MKT_PRC               |      4 |      1 |   129   (0)|      2 |00:00:00.01 |       4 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DS"."SRC_ID"="F_MKT_P"."SRC_ID")
   3 - storage(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("DS"."RECORD_DT_END"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
       filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("DS"."RECORD_DT_END"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
  10 - access("MM"."INSTR_ID"=12621381)
  13 - access("L"."ORIGINAL_ID"=12621381)
  14 - access("MM"."INSTR_ID"="L"."INSTR_ID")
       filter("MM"."INSTR_ID" IS NOT NULL)
  18 - access("INSTR_ID"=12621381)
  20 - access("MM"."INSTR_ID"="ORIGINAL_ID")
       filter("MM"."INSTR_ID" IS NOT NULL)
  23 - access("L"."ORIGINAL_ID"=)
       filter("L"."ORIGINAL_ID"<>0)
  25 - access("INSTR_ID"=12621381)
  26 - access("MM"."INSTR_ID"="L"."INSTR_ID")
       filter("MM"."INSTR_ID" IS NOT NULL)
  30 - access("INSTR_ID"=12621381)
  31 - access("L"."ORIGINAL_ID"=)
       filter("L"."ORIGINAL_ID"<>0)
  33 - access("INSTR_ID"=12621381)
  34 - access("L"."ORIGINAL_ID"=12621381)
  37 - access("F_MKT_P"."LST_ID"="DT"."THE_ID" AND "F_MKT_P"."PRC_DT">=TO_DATE(' 2008-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "F_MKT_P"."PRC_DT"<=TO_DATE(' 2008-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(("F_MKT_P"."PRC_DT"<=TO_DATE(' 2008-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F_MKT_P"."PRC_DT">=TO_DATE(' 2008-02-05 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
  38 - filter(((SYS_EXTRACT_UTC(INTERNAL_FUNCTION("F_MKT_P"."RECORD_DT_END"))>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) OR "F_MKT_P"."RECORD_DT_END" IS NULL) AND CASE
              WHEN ((-200)=(-100) AND "F_MKT_P"."COUNTRY"='US') THEN 1 WHEN ((-200)=(-200) AND "F_MKT_P"."COUNTRY"='CA') THEN 1 WHEN ((-200)=(-300) AND ("F_MKT_P"."COUNTRY"='CA' OR
              "F_MKT_P"."COUNTRY"='US')) THEN 1 WHEN ((-200)=(-500) AND ("F_MKT_P"."COUNTRY"='CA' OR "F_MKT_P"."COUNTRY"='US')) THEN 1 ELSE 0 END =1))


95 rows selected.

Chris Saxon
May 26, 2022 - 1:09 pm UTC

It's picking the full scan because that plan has a lower cost (20204 vs 23284).

Reading F_MKT_PRC is expensive - the full scan reads ~11 thousand rows, but does 1.4 million I/Os to get them! That's around 1,000 gets/row!

Are there lots of long text columns on this table?
Or lots of chained/migrated rows?
Or lots of wasted space?

Primary Key Index not getting picked on Partitioned Table

A reader, May 26, 2022 - 3:07 am UTC

Hi Connor,

Continuation from the previous post...

Here is some additional details.

DB version: 19.11.0.0

Queries are provided below -

Actual query:
============
SELECT /*+ gather_plan_statistics */ '"' || ds.SOURCE_MNEMONIC || '",'
                        || DECODE(ds.RTS_SOURCE,null,'"!NA"',ds.RTS_SOURCE) || ','
                        || DECODE(F_MKT_P.CURRENCY,null,'"!NA"', '"' || F_MKT_P.CURRENCY ||'"'),
                        DECODE(F_MKT_P.LAST_TRADE_PRICE,null,'"!NA"',F_MKT_P.LAST_TRADE_PRICE) || ','
                        || DECODE(F_MKT_P.LAST_TRADE_PRICE_TYPE,null,'"!NA"',F_MKT_P.LAST_TRADE_PRICE_TYPE)
                        || ',"!NA","!NA","!NA","!NA","!NA",'
                        || DECODE(F_MKT_P.LAST_BID_PRICE,null,'"!NA"',F_MKT_P.LAST_BID_PRICE) || ',"!NA"',
                        F_MKT_P.PRC_DT, DECODE(F_MKT_P.COUNTRY,null,'"!NA"','"' || F_MKT_P.COUNTRY || '"'), ds.SOURCE_TYPE
                FROM MHIST.F_MKT_PRC F_MKT_P
                INNER JOIN (
                        SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID = 12621381
                        UNION SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID in (
                            SELECT L.INSTR_ID from MHIST.INSTR_LNKGE L WHERE L.ORIGINAL_ID = 12621381 AND L.ORIGINAL_ID != 0)
                        UNION SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID in (
                            SELECT ORIGINAL_ID from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381)
                        UNION SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID in (
                                SELECT L.INSTR_ID from MHIST.INSTR_LNKGE L
                                WHERE L.ORIGINAL_ID = ( SELECT ORIGINAL_ID from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381)
                                AND L.ORIGINAL_ID != 0)
                        UNION SELECT 12621381 AS the_id FROM DUAL
                        UNION SELECT ORIGINAL_ID AS the_id from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381
                        UNION
                        SELECT L.INSTR_ID AS the_id from MHIST.INSTR_LNKGE L
                                WHERE L.ORIGINAL_ID = ( SELECT ORIGINAL_ID from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381)
                                AND L.ORIGINAL_ID != 0
                        UNION
                        SELECT L.INSTR_ID AS the_id from MHIST.INSTR_LNKGE L WHERE L.ORIGINAL_ID = 12621381 AND L.ORIGINAL_ID != 0
                ) dt ON (F_MKT_P.LST_ID = dt.the_id)
                INNER JOIN MHIST.D_SOURCE ds ON ds.SRC_ID = F_MKT_P.SRC_ID AND ds.RECORD_DT_END > SYSTIMESTAMP
                WHERE F_MKT_P.PRC_DT BETWEEN TO_DATE('20080205', 'yyyymmdd') AND TO_DATE('20080206', 'yyyymmdd')
                AND (F_MKT_P.RECORD_DT_END IS null OR F_MKT_P.RECORD_DT_END > SYSTIMESTAMP)
                --AND (F_MKT_P.LAST_TRADE_PRICE_TYPE IS NULL OR F_MKT_P.LAST_TRADE_PRICE_TYPE != REGIONAL_EXCHANGE_TYPE)
                AND (CASE
                    WHEN (-200 = -100 AND F_MKT_P.COUNTRY = 'US') THEN 1
                    WHEN (-200 = -200 AND F_MKT_P.COUNTRY = 'CA') THEN 1
                    WHEN (-200 = -300 AND F_MKT_P.COUNTRY IN ('CA', 'US') ) THEN 1
                    WHEN (-200 = -500 AND F_MKT_P.COUNTRY IN ('CA', 'US') ) THEN 1
                    ELSE 0
                    END
                ) = 1
                ORDER BY F_MKT_P.PRC_DT DESC, ds.SOURCE_PRIORITY ASC, F_MKT_P.VOLUME_RANK ASC, F_MKT_P.COUNTRY DESC, F_MKT_P.RECORDED_DATE_START DESC;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));

With INDEX HINT:
==============

SELECT /*+ gather_plan_statistics INDEX(F_MKT_P F_MKT_PRC_PK) */ '"' || ds.SOURCE_MNEMONIC || '",'
                        || DECODE(ds.RTS_SOURCE,null,'"!NA"',ds.RTS_SOURCE) || ','
                        || DECODE(F_MKT_P.CURRENCY,null,'"!NA"', '"' || F_MKT_P.CURRENCY ||'"'),
                        DECODE(F_MKT_P.LAST_TRADE_PRICE,null,'"!NA"',F_MKT_P.LAST_TRADE_PRICE) || ','
                        || DECODE(F_MKT_P.LAST_TRADE_PRICE_TYPE,null,'"!NA"',F_MKT_P.LAST_TRADE_PRICE_TYPE)
                        || ',"!NA","!NA","!NA","!NA","!NA",'
                        || DECODE(F_MKT_P.LAST_BID_PRICE,null,'"!NA"',F_MKT_P.LAST_BID_PRICE) || ',"!NA"',
                        F_MKT_P.PRC_DT, DECODE(F_MKT_P.COUNTRY,null,'"!NA"','"' || F_MKT_P.COUNTRY || '"'), ds.SOURCE_TYPE
                FROM MHIST.F_MKT_PRC F_MKT_P
                INNER JOIN (
                        SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID = 12621381
                        UNION SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID in (
                            SELECT L.INSTR_ID from MHIST.INSTR_LNKGE L WHERE L.ORIGINAL_ID = 12621381 AND L.ORIGINAL_ID != 0)
                        UNION SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID in (
                            SELECT ORIGINAL_ID from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381)
                        UNION SELECT MM.MARKET_ID AS the_id FROM IMSG.MKT_MAST MM WHERE MM.INSTR_ID in (
                                SELECT L.INSTR_ID from MHIST.INSTR_LNKGE L
                                WHERE L.ORIGINAL_ID = ( SELECT ORIGINAL_ID from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381)
                                AND L.ORIGINAL_ID != 0)
                        UNION SELECT 12621381 AS the_id FROM DUAL
                        UNION SELECT ORIGINAL_ID AS the_id from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381
                        UNION
                        SELECT L.INSTR_ID AS the_id from MHIST.INSTR_LNKGE L
                                WHERE L.ORIGINAL_ID = ( SELECT ORIGINAL_ID from MHIST.INSTR_LNKGE WHERE INSTR_ID = 12621381)
                                AND L.ORIGINAL_ID != 0
                        UNION
                        SELECT L.INSTR_ID AS the_id from MHIST.INSTR_LNKGE L WHERE L.ORIGINAL_ID = 12621381 AND L.ORIGINAL_ID != 0
                ) dt ON (F_MKT_P.LST_ID = dt.the_id)
                INNER JOIN MHIST.D_SOURCE ds ON ds.SRC_ID = F_MKT_P.SRC_ID AND ds.RECORD_DT_END > SYSTIMESTAMP
                WHERE F_MKT_P.PRC_DT BETWEEN TO_DATE('20080205', 'yyyymmdd') AND TO_DATE('20080206', 'yyyymmdd')
                AND (F_MKT_P.RECORD_DT_END IS null OR F_MKT_P.RECORD_DT_END > SYSTIMESTAMP)
                --AND (F_MKT_P.LAST_TRADE_PRICE_TYPE IS NULL OR F_MKT_P.LAST_TRADE_PRICE_TYPE != REGIONAL_EXCHANGE_TYPE)
                AND (CASE
                    WHEN (-200 = -100 AND F_MKT_P.COUNTRY = 'US') THEN 1
                    WHEN (-200 = -200 AND F_MKT_P.COUNTRY = 'CA') THEN 1
                    WHEN (-200 = -300 AND F_MKT_P.COUNTRY IN ('CA', 'US') ) THEN 1
                    WHEN (-200 = -500 AND F_MKT_P.COUNTRY IN ('CA', 'US') ) THEN 1
                    ELSE 0
                    END
                ) = 1
                ORDER BY F_MKT_P.PRC_DT DESC, ds.SOURCE_PRIORITY ASC, F_MKT_P.VOLUME_RANK ASC, F_MKT_P.COUNTRY DESC, F_MKT_P.RECORDED_DATE_START DESC;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));

Primary Key Index not getting picked on Partitioned Table

A reader, May 26, 2022 - 1:46 pm UTC

Hi Chris,

//Are there lots of long text columns on this table?
Or lots of chained/migrated rows?
Or lots of wasted space?

No LOB/LONG columns on any of the tables involved in the query.

Index range scan on Local Primary index is the faster access path.. and the query timings are here.

Full table partition scan - Elapsed: 00:01:13.35

With Index hint - Elapsed: 00:00:00.13

1) I rebuilt the F_MKT_PRC table/index and loaded the data .. still no difference.


Local Primary key index is on the following column.

PRC_DT is the partition key.

"MHIST"."F_MKT_PRC_PK" ON "MHIST"."F_MKT_PRC" ("LST_ID", "SRC_ID", "SNAP_ID", "PRC_DT", "RECORD_DT_START"

OWNER TABLE_NAME NUM_ROWS CHAIN_CNT
---------- ------------------------- ---------- ----------
MHIST F_MKT_PRC 7289890059 0

SQL> desc F_MKT_PRC
Name Null? Type
----------------------------------------- -------- ----------------------------
LST_ID NOT NULL NUMBER(19)
SRC_ID NOT NULL NUMBER(19)
SNAP_ID NOT NULL NUMBER(19)
PRC_DT NOT NULL DATE
VOLUME_RANK NUMBER(19)
IDENTIFIER_TYPE NOT NULL NUMBER(10)
IDENTIFIER NOT NULL VARCHAR2(30)
COUNTRY CHAR(2)
CURRENCY CHAR(3)
LAST_TRADE_PRICE NUMBER(38,15)
LAST_BID_PRICE NUMBER(38,15)
LAST_ASK_PRICE NUMBER(38,15)
HIGH_PRICE NUMBER(38,15)
LOW_PRICE NUMBER(38,15)
VWAP NUMBER(38,15)
VOLUME NUMBER(38,15)
CASH_FLOW NUMBER(38,15)
TRADED_VALUE NUMBER(38,15)
BLOCK_VOLUME NUMBER(19)
NUM_TRADES NUMBER(19)
NUM_BLOCK_TRADES NUMBER(19)
OPEN_PRICE NUMBER(38,15)
EXCHANGE_CLOSE NUMBER(38,15)
NAV NUMBER(38,15)
MID_PRICE NUMBER(38,15)
NOMINAL_PRICE NUMBER(38,15)
LAST_TRADE_PRICE_TYPE NUMBER(10)
EXCHANGE_CLOSE_TYPE NUMBER(10)
SOURCE_COMMENT VARCHAR2(40)
OTHER_PRICE NUMBER(38,15)
RECORDED_DATE_END TIMESTAMP(6)
LOADED_BY VARCHAR2(100)
RECORD_DT_START NOT NULL TIMESTAMP(6)
OPEN_INTEREST NUMBER(38,15)


Chris Saxon
May 30, 2022 - 12:32 pm UTC

I should have been clearer - by "long text" I'm including VARCHAR2/CHAR columns with high limits that often have values up to/near this limit.

If you've rebuilt the table (what do you mean by that?) and it's still the same size it suggests the table really is this large.

In which case creating a covering index (one that includes all the columns in F_MKT_PRC that this query accesses) may be worth looking into.

I would also look at all those UNIONs. These could be replaced by UNION ALL (avoiding a sort) and refactored to reduce how many times it reads INSTR_LNKGE.

As I said, it's not picking the index because the optimizer assigns this path a higher cost. If everything else really is "the same" in the other environment, check if the other database has any profiles or baselines to force the index path.

Regarding gets/row calculation

Rajeshwaran Jeyabal, June 03, 2022 - 3:52 am UTC


Reading F_MKT_PRC is expensive - the full scan reads ~11 thousand rows, but does 1.4 million I/Os to get them! That's around 1,000 gets/row!

but i see only 100 gets/row not 1000 gets/row like you said.
demo@XEPDB1> select 1400000 / 11000 from dual ;

1400000/11000
-------------
   127.272727

Chris Saxon
June 06, 2022 - 1:53 pm UTC

Ah yes - you're correct. Good spot.

The underlying point still stands though - that's a huge number of gets/row. Ideally, you want to want the ratio to be the other way around i.e. 100 rows/get. Particularly for full table scans.

Re: Primary Key Index not getting picked on Partitioned Table

Narendra, June 06, 2022 - 11:12 pm UTC

Hello,

I have to admit I don't have any pointers or answers but I am always intrigued by the cases where what seems quite obvious (to us, humans) as a better execution plan is not picked up by Optimizer, due to costing of access paths.
I have 2 questions about this case:
1. Is it possible that while the execution plan indicates a "smart scan", in real times, there is no smart scan taking place, for what ever reason? &
2. Is there a chance that optimizer is assigning lower cost due to assuming (??) there will be "smart scan" and favouring FTS over indexed access (which, in this case, could be deemed expensive due to SQL not referring all columns of indexes??) ?

In other words, is the optimizer likely to favour FTS (by assigning lower cost) when using exadata over indexed access path?

Thanks
Connor McDonald
June 07, 2022 - 3:13 am UTC

In other words, is the optimizer likely to favour FTS (by assigning lower cost) when using exadata over indexed access path?

Yes. That is not an "exadata" thing per se, but is probably most noticeable there. But if you had (say) a storage array that made full scans blazingly fast but indexes were not so, then (assuming you had some system stats in place) you would (correctly) see the database lean toward more full scans.

The optimizer is always trying to yield the best *response time*.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.