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 ; 

CodePudding user response：

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.