For the following query , I tried creating index on table tmp.req_index_cont_t with columns indcont_key_1 and ind_no but still optimizer is performing full table scan , I am not getting how to resolve this issue , please guide :
CREATE INDEX tmp.REQ_CONT_T_IDX ON tmp.req_index_cont_t (ind_no, indcont_key_1); select distinct rit.item_no as item_no, rit.item_type as item_type, rit.ind_no as ind_no, rit.delete_date as req_ind_delete_date, indcnt.delete_date as req_ind_cont_delete_date from tmp.req_index_cont_t indcnt, tmp.req_index_t rit where rit.ind_no = indcnt.ind_no and indcnt.indcont_key_1 <> 'DN' and rit.ind_state = 'Approved' group by rit.item_no, rit.item_type, rit.ind_no, indcnt.delete_date, rit.delete_date ;
Advertisement
Answer
There’s not enough information here to answer your question. Why do you think a full table scan is not the best option for this query? I assume you’re upset that it’s doing a full table scan on req_index_cont_t
because that’s the one you put an index on.
As an educated guess, I’d say it’s because you have indcnt.delete_date
in the select list but not the index. That means Oracle will have to fetch the entire row in question and has decided against using the index.