I am forcing the below sql to use the index XM240P0 in the execution. But there is no change the explain plan and the index is not being used. I need to tune the query as it is doing a full table scan. The explain plan is as shown below. Please advice. I will be glad to furnish any other details you may want
select /*+ index(XM240P0) */
a.scm_id, a.sc_evt_id, a.cre_dttm, a.SC_EVT_STATUS_FLG,
c.dst_id, c.gl_acct, c.amount
where a.sc_evt_id = b.SC_EVT_ID
and b.ft_id = c.ft_id
--and d.ft_id = c.ft_id
and a.sc_evt_type_cd = 'EO ALLOC'
and c.dst_id like '2010 EO%'
and a.scm_id = '0004720000'
SELECT STATEMENT CHOOSECost: 6 Bytes: 141 Cardinality: 1
7 TABLE ACCESS BY INDEX ROWID CISADM.CI_FT_GL Cost: 3 Bytes: 63 Cardinality: 1
6 NESTED LOOPS Cost: 6 Bytes: 141 Cardinality: 1
4 NESTED LOOPS Cost: 3 Bytes: 78 Cardinality: 1
1 TABLE ACCESS FULL CISADM.CI_SC_EVT_FT Cost: 2 Bytes: 28 Cardinality: 1
3 TABLE ACCESS BY INDEX ROWID CISADM.CI_SC_EVT Cost: 1 Bytes: 50 Cardinality: 1
2 INDEX UNIQUE SCAN UNIQUE CISADM.XT242P0 Cost: 1 Cardinality: 1
5 INDEX RANGE SCAN UNIQUE CISADM.XT113P0 Cost: 3 Cardinality: 2
XT242P0 is created on sc_evt_id
XT113P0 is a composite index (FT_ID, GL_SEQ_NBR)