Index used when query process on oracle database

indexoraclestatistics

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

Best Answer

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.

Related Question