I have one large volume in which is use index skip scan and impacting my application and going in long running.
SELECT * FROM TRANSACTIONLOG WHERE (DEL_ID=:"SYS_B_00" OR
ORG_DEL_ID =:"SYS_B_01") AND (PAYER_MOBILE_NO=:"SYS_B_02" OR
PAYEE_MOBILE_NO=:"SYS_B_03") AND TXN_STATUS IN
(:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SY
S_B_09") AND TXN_TYPE IN (:"SYS_B_10", :"SYS_B_11",
:"SYS_B_12", :"SYS_B_13", :"SYS_B_14", :"SYS_B_15") ORDER BY
TXN_LOGGED_DATE DESC;
create index IDX_TRANSACTIONLOG on UPI_TRANSACTIONLOG(DEL_ID,ORG_DEL_ID,PAYER_MOBILE_NO,PAYEE_MOBILE_NO,TXN_STATUS,TXN_TYPE,TXN_LOGGED_DATE) INITRANS 10 ONLINE;
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1914 (100)| |
| 1 | SORT ORDER BY | | 2 | 10012 | 1914 (1)| 00:00:01 |
| 2 | VIEW | VW_ORE_F1FFCE3C | 2 | 10012 | 1913 (1)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TRANSACTIONLOG | 1 | 473 | 1842 (1)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TRANSACTIONLOG | 1 | | 1842 (1)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| TRANSACTIONLOG | 1 | 473 | 71 (0)| 00:00:01 |
|* 7 | INDEX SKIP SCAN | IDX_TRANSACTIONLOG | 1 | | 71 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEL_ID"=:SYS_B_00)
filter((("PAYER_MOBILE_NO"=:SYS_B_02 OR "PAYEE_MOBILE_NO"=:SYS_B_03) AND
INTERNAL_FUNCTION("TXN_TYPE") AND INTERNAL_FUNCTION("TXN_STATUS")))
7 - access("ORG_DEL_ID"=:SYS_B_01)
filter(("ORG_DEL_ID"=:SYS_B_01 AND ("PAYER_MOBILE_NO"=:SYS_B_02 OR "PAYEE_MOBILE_NO"=:SYS_B_03)
AND INTERNAL_FUNCTION("TXN_TYPE") AND INTERNAL_FUNCTION("TXN_STATUS") AND (LNNVL("DEL_ID"=:SYS_B_00) OR
LNNVL("PAYER_MOBILE_NO"=:SYS_B_02)) AND (LNNVL("DEL_ID"=:SYS_B_00) OR
LNNVL("PAYEE_MOBILE_NO"=:SYS_B_03))))
Please help to avoid index skip scan and suggest any change required .