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")
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/