index used when query process on oracle database
Any Relational Database, not just Oracle, will work out for itself what it considers to be the &quot;best&quot; way of getting the data you ask for.
It bases its &quot;working out&quot; 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 &quot;stale&quot;, that is to say &quot;out of date&quot; 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, &quot;believing&quot; it (based on the statistics) to be empty.  Updating the statistics, showing the DBMS the &quot;truth&quot; about the table's contents allows it to make a better assessment of how to &quot;get at&quot; rows in that table and would, most likely, improve things dramatically.