CODEWITHSUNDEEP
sqloracle-databaseperformanceoracle11g
onlinejava
onlinejava

Reputation: 183

Query does full table scan even with indexes

I am having a simple query with all joined columns indexed but still i receive a full table scan Following is the query

   select rsb.REP_STATUS_BRIDGE_ID
   from REP_STATUS_BRIDGE_DETAILS rsd,
        rep_status_bridge rsb
   where rsd.REP_STATUS_BRIDGE_ID = rsb.REP_STATUS_BRIDGE_ID;

the columns REP_STATUS_BRIDGE_ID is indexed in both the tables, following is the explain plan

Explain plan at the following link

Please assist me in resolving this issue

As many Folks have asked this is the query that is taking forever to load 

select count(rsb.REP_STATUS_BRIDGE_ID) from 
pcfc_dba.rep_pass rp,
pcfc_dba.rep_status_bridge rsb,
pcfc_dba.REP_STATUS_BRIDGE_DETAILS rsd,
pcfc_dba.rep_status_ref rsf
where trunc(rp.APPR_ACTION_END_DATE)>=to_date('01/02/2017','dd/MM/yyyy') 
and trunc(rp.APPR_ACTION_END_DATE)<=to_date('06/02/2107','dd/MM/yyyy')
and  rp.REP_STATUS_BRIDGE_ID = rsb.REP_STATUS_BRIDGE_ID
and   rsb.REP_STATUS_BRIDGE_ID=rsd.REP_STATUS_BRIDGE_ID
and  rsf.REP_STATUS_REF_ID=rsd.REP_STATUS_REF_ID;

The explain plan

Upvotes: 1

Views: 6313

Answers (1)

Michael Piankov
Michael Piankov

Reputation: 1997

I think the main problem is a stale statistic as was said above. The table REP_STATUS_BRIDGE_DETAILS has 1.3 mil records as you said before. But in plan we see full table scan expect to get only 310 rows( I suppose it's rows, but I don't see a header). Could you please gather statistics ec.: 

begin 
DBMS_STATS.GATHER_TABLE_STATS (
   ownname          => USER, 
   tabname          => 'REP_STATUS_BRIDGE_DETAILS', 
   cascade          => true);
end;
/

and then check num_rows from all_tables view

 select t.owner, t.table_name, t.num_rows 
  from all_tables t 
 where t.table_name in ('REP_STATUS_BRIDGE_DETAILS');

It's take in account statistic's data. After statics will be gathered you should get a different plans for both queries.

Upvotes: 1

Related Questions