Hi,
Please look into the below plan and suggest how to avoid the filter scan on the table ..
SQL_ID 6gh552akfxjf1, child number 0
-------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 542 (100)| | | | 1 |00:00:00.04 | 19382 |
| 1 | SORT AGGREGATE | | 1 | 1 | 91 | | | | | 1 |00:00:00.04 | 19382 |
|* 2 | FILTER | | 1 | | | | | | | 0 |00:00:00.04 | 19382 |
| 3 | NESTED LOOPS | | 1 | 1 | 91 | 542 (0)| 00:00:01 | | | 0 |00:00:00.04 | 19382 |
| 4 | PARTITION RANGE ITERATOR | | 1 | 1 | 58 | 536 (0)| 00:00:01 | KEY | KEY | 2048 |00:00:00.01 | 2998 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_W_TXN | 2 | 1 | 58 | 536 (0)| 00:00:01 | KEY | KEY | 2048 |00:00:00.01 | 2998 |
|* 6 | INDEX RANGE SCAN | IDX_W_TXN_ACCNM | 2 | 504 | | 30 (0)| 00:00:01 | KEY | KEY | 2987 |00:00:00.01 | 38 |
| 7 | PARTITION RANGE ITERATOR | | 2048 | 1 | 33 | 6 (0)| 00:00:01 | KEY | KEY | 0 |00:00:00.02 | 16384 |
|* 8 | INDEX RANGE SCAN | PK_W_PRIORITY_DETAIL | 4096 | 1 | 33 | 6 (0)| 00:00:01 | KEY | KEY | 0 |00:00:00.02 | 16384 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5428C7F1
5 - SEL$5428C7F1 / T_W_TXN@SEL$2
6 - SEL$5428C7F1 / T_W_TXN@SEL$2
8 - SEL$5428C7F1 / T_W_PRIORITY_DETAIL@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((TO_DATE(:B2,'mm/dd/yyyy hh24:mi:ss')>=TO_DATE(:B3,'mm/dd/yyyy hh24:mi:ss') AND TO_DATE(:B2,'mm/dd/yyyy hh24:mi:ss')>=TO_DATE(:B3,'mm/dd/yyyy hh24:mi:ss')))
5 - filter(("F_OPERATION"='DB' AND CASE WHEN :B1='ALL' THEN 1 WHEN (:B1<>'ALL' AND "F_CANCEL_TXN_ID" IS NULL) THEN 1 ELSE 0 END =1 AND CASE WHEN (:B1='ALL' AND
("F_STATUS"='SUCCESS' OR "F_STATUS"='CANCELLED')) THEN 1 WHEN (:B1<>'ALL' AND "F_STATUS"='SUCCESS') THEN 1 ELSE 0 END =1 AND ("F_IS_GAME_TXN"='Y' OR
INTERNAL_FUNCTION("F_TXNTYPE"))))
6 - access("F_ACC_NAME"=:B4 AND "F_CREATED_TS">=TO_DATE(:B3,'mm/dd/yyyy hh24:mi:ss') AND "F_CREATED_TS"<=TO_DATE(:B2,'mm/dd/yyyy hh24:mi:ss'))
8 - access("F_ID"="F_W_TXN_ID" AND "F_SUB_WLT_TYPE"='FreeBet' AND "F_CREATED_TS">=TO_DATE(:B3,'mm/dd/yyyy hh24:mi:ss') AND "F_CREATED_TS"<=TO_DATE(:B2,'mm/dd/yyyy
hh24:mi:ss'))
filter(("F_CREATED_TS">=TO_DATE(:B3,'mm/dd/yyyy hh24:mi:ss') AND "F_CREATED_TS"<=TO_DATE(:B2,'mm/dd/yyyy hh24:mi:ss')))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
4 - "F_ID"[NUMBER,22]
5 - "F_ID"[NUMBER,22]
6 - "T_W_TXN".ROWID[ROWID,10]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
1 - SEL$5428C7F1
- leading(wlt,wpd)
5 - SEL$5428C7F1 / T_W_TXN@SEL$2
- index_rs(wlt IDX_W_TXN_ACCNM)
8 - SEL$5428C7F1 / T_W_PRIORITY_DETAIL@SEL$3
- index_rs(wpd PK_W_PRIORITY_DETAIL )
- use_nl(wpd)