Hi All,
i had a query like
" SELECT BATCH_NO
FROM <Table_name>
WHERE NVL(RTRIM(ACQ_INST_ID),' ') = NVL(RTRIM(:b1),' ')
AND NVL(RTRIM(FWD_INST_ID),' ') = NVL(RTRIM(:b2),' ')
AND NVL(RTRIM(CA_ID_CODE),' ') = NVL(RTRIM(:b3),' ')
AND NVL(RTRIM(CA_TERM_ID),' ') = NVL(RTRIM(:b4),' ') "

and we have a index on the table as
CREATE UNIQUE INDEX in_ol_pos_batch_mast_1
ON Table_name
( acq_inst_id,
fwd_inst_id,
ca_id_code,
ca_term_id )

But the query does not uses index rather it does a full table scan,we have tried adding a function based index also but even that is not helping.
Is it beacuse of RTRIM,Could you suggest some thing on this.How we can make the query or index so that index is used.

Regards,Navin

Oracle LazyDBA home page