I a new one using and learn about oracle database. currently i have sql query like this

select attend_code from attadanstatusdetail
where attend_id = 'attendidcode001'
and company_id = 168

currently i have 2 schema on one instance or database with same index. From explain results schema1 using index range scan which is this is good for me. but at schema2 using index fast full scan.

my question

  1. whats going on at schema2, why schema2 not using index range scan ?
  2. after I running exec dbms_stats.gather_schema_stats('schema12','indexname') on schema2 i got same results from explain this is index range scan ?

can explain more to me about thats thanks


Any Relational Database, not just Oracle, will work out for itself what it considers to be the "best" way of getting the data you ask for.

It bases its "working out" on many, many factors, but most of these are taken from the statistics that it automatically gathers about every table, index, etc. The fact that your query plan changed when you gathered statistics on the table suggests that the statistics were "stale", that is to say "out of date" compared to what was actually in the table.

An extreme example of this might be if you created an empty table last thing one evening. The statistics job generally runs overnight and would note that the table had no rows in it. If, the following morning you loaded it with millions of records, it is quite likely that any query against that table would simply try to Table Scan it, "believing" it (based on the statistics) to be empty. Updating the statistics, showing the DBMS the "truth" about the table's contents allows it to make a better assessment of how to "get at" rows in that table and would, most likely, improve things dramatically.

  • hi phill thanks for advice. so it means running the gather_index_stats or etc is good if I have same issue ? – febry Oct 1 '20 at 8:56
  • 1
    Oracle should be doing this automatically. Make sure the statistics jobs (within the database) are running correctly. But, if you add or remove large amounts of data in one go, it doesn't hurt to give the database a "nudge" and get it to re-gather them, afterwards. – Phill W. Oct 1 '20 at 10:12
  • the statisitcs jobs running automatically as default ? – febry Oct 5 '20 at 2:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.