It is critical to understand that a full-table scan is a symptom of a possible sub-optimal SQL plan.
While not all full scans are bad to performance,
full table scans are a symptom of other common tuning problems like missing indexes and sub-optimal schema statistics (dbms_stats). For small tables, a full-table scan is better than a full-scan.
However, for a large-table, a full-table scan should always be examined as a potential problem.
Full scan I/O costs less than index I/O
When Oracle reads a table front to back during a full-table scan, we save a great deal of disk latency. Remember, 96% of the disk latency is the time required for the read-write head to move itself under the proper cylinder.
Once there, the read-write head sits idle and the platter spins beneath it, and Oracle can read-in data blocks as fast as the disk platter can spin, a processed called the db file scattered read.
Oracle Checks Library Cache
Once the SQL has been parsed, Oracle checks the library cache to see if there is an index on the STATUS column of the POPULATION table. Since there is not an index, Oracle creates an execution plan that requires every column of the population table to be read, looking for STATUS ='Convicted Felon' Oracle now proceeds to read every row of the population table, causing a huge amount of I/O and work for the Oracle database. If there is not an index on the STATUS column,
Oracle will have no choice but to search every row in the table to find your Felons. If you had built an index on the STATUS column, the query could have been executed very quickly using the index to go directly to the rows that you want.