DB version: 19.13
The below UPDATE statement on PKTD_AP table is going for a costly 'INDEX SKIP SCAN' + 'INDEX RANGE SCAN'.
Is there any way to have a more efficient plan ? I am open to creating a new index if it is needed (or any other suggestion).
PKTD_AP table is 10 GB in size and the optimizer stats of the below table are up-to-date.
Details of the table including cardinality of the columns in PKTD_PT_ID1 index listed below.
PKTD_PT_ID1 index is a composite index made from INVOICE_NR,BOOKING_NUMBER columns.
I noticed that 76% of BOOKING_NUMBER column values are NULL in this table. And 12% of INVOICE_NR are NULLs.
But, from what I understand, NULLs are not stored in B-Tree indexes, so the NULLs in the columns shouldn't be a problem, I guess.
Application guys have informed they cannot include PT_ID in the WHERE clause (like AND PT_ID BETWEEN a and b;) otherwise this could have been a more efficient INDEX RANGE SCAN on PKTD_AP_PK index.
UPDATE D00PKTD01.PKTD_AP
SET ZETARL_DT = :1,
ZETARL_REFERENTIE = :2,
BKH_BEDRAG = :3,
BKH_IBAN = :4,
ROWVERSION = (ROWVERSION + :5),
BOOKING_NUMBER = :6
WHERE ((BOOKING_NUMBER = :7) OR (INVOICE_NR = :8));
Execution plan
select * from TABLE(dbms_xplan.display_cursor(SQL_ID => '5z4adfz5m6qrc', CURSOR_CHILD_NO => NULL));
Plan hash value: 4867721539
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 189K(100)| |
| 1 | UPDATE | PKTD_AP | | | | |
| 2 | CONCATENATION | | | | | |
|* 3 | INDEX RANGE SCAN| PKTD_PT_ID1 | 10 | 770 | 4 (0)| 00:00:01 |
|* 4 | INDEX SKIP SCAN | PKTD_PT_ID1 | 12 | 924 | 189K (1)| 00:00:08 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("INVOICE_NR"=:8)
4 - access("BOOKING_NUMBER"=:7)
filter(("BOOKING_NUMBER"=:7 AND LNNVL("INVOICE_NR"=:8)))
BTW
Execution plan remains the same when columns in the WHERE clause are flipped
WHERE ((INVOICE_NR = :7) OR (BOOKING_NUMBER = :8));
-- Details on PKTD_AP table.
SQL> desc D00PKTD01.PKTD_AP
Name Null? Type
------------------------------------------- -------- ------------------------------------
1 PT_ID NOT NULL NUMBER(20)
2 BEDRAG NUMBER(12,4)
3 BESTANDSNAAM VARCHAR2(50 CHAR)
4 ZETARL_DT DATE
5 ZETARL_REFERENTIE VARCHAR2(124 CHAR)
6 BIC VARCHAR2(11 CHAR)
7 IBAN VARCHAR2(40 CHAR)
8 INVOICE_NR VARCHAR2(15 CHAR)
9 MUNT_CD VARCHAR2(3 CHAR)
10 OMSCHRIJVING VARCHAR2(124 CHAR)
11 ARTS_NUMMER VARCHAR2(12 CHAR)
12 TRANSACTXN_DT DATE
13 TRANSACTXN_STATUS_CD NOT NULL NUMBER(2)
14 TRANSACTXN_STATUS_DT NOT NULL DATE
15 TRANSACTXN_TYPE NOT NULL VARCHAR2(1 CHAR)
16 GMD_ID NOT NULL NUMBER(19)
17 CRE_USER NOT NULL VARCHAR2(48 CHAR)
18 CRE_DT NOT NULL DATE
19 WIJZ_USER VARCHAR2(48 CHAR)
20 WIJZ_DT DATE
21 ROWVERSION NOT NULL NUMBER(9)
22 BOOKING_NUMBER NUMBER(10)
23 BKH_IBAN VARCHAR2(40)
24 BKH_BEDRAG NUMBER(12,4)
25 NOMENCLATUUR_CD NUMBER(6)
26 PICKSLOTX_NUMBER VARCHAR2(8 CHAR)
SQL> select count(*) from D00PKTD01.PKTD_AP;
COUNT(*)
----------
42444910
-- Only 8.85% are distinct values
SQL> select count(distinct(INVOICE_NR)) from D00PKTD01.PKTD_AP;
COUNT(DISTINCT(INVOICE_NR))
---------------------------
3757150
-- Only 2% distinct values
SQL> select count(distinct(BOOKING_NUMBER)) from D00PKTD01.PKTD_AP;
COUNT(DISTINCT(BOOKING_NUMBER))
-------------------------------
833169
-- Both of these columns have a lot of NULLs
-- 76% of BOOKING_NUMBER are NULLs
SQL> select count(*) from D00PKTD01.PKTD_AP where BOOKING_NUMBER is null;
COUNT(*)
----------
32277347
Elapsed: 00:00:06.91
SQL>
-- 12% of INVOICE_NR are NULLs
SQL> select count(*) from D00PKTD01.PKTD_AP where INVOICE_NR is null;
COUNT(*)
----------
5134679
-- This query was run 1 hour after the above queries
SQL> select count(*) from (select distinct INVOICE_NR,BOOKING_NUMBER from D00PKTD01.PKTD_AP );
COUNT(*)
----------
4047709
Indexes of PKTD_AP table with its columns by position. Only PKTD_PT_ID1 is a composite index. The rest are single column indexes.
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_PT_ID1 INVOICE_NR,BOOKING_NUMBER
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_PT_ID2 BESTANDSNAAM
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_PT_ID3 IBAN
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_PT_ID4 TRANSACTXN_DT
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_PT_ID5 ARTS_NUMMER
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_PT_ID6 PICKSLOTX_NUMBER
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_AP_IF1 GMD_ID
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_AP_IF2 TRANSACTXN_STATUS_CD
INDEX_NAME COLUMN_NAME
----------------------------------- -----------------------------------
PKTD_AP_PK PT_ID
9 rows selected.