FULL Table Scan(DB file Scattered read) Vs INDEX Scan(DB file sequential read)

DBAs and Developers often look at FULL Table Scans or INDEX scans happening within their database and code respectively. It is often assumed that FULL Table Scan would make the SQL statement perform slower.

Let us try and clear this misconception. 
FULL Table Scan(DB file scattered read) is not necessarily an expensive operation as compared to INDEX scan. It all depends on what the SQL is trying to do. If the SQL statement is returning more than 10% of the total volume in the table, FULL Table Scan is actually faster than Index Scan. This is because an index is scanned one block at a time whereas in-case of FULL Table Scan, multiple blocks, as determined by 'DB_file_multiblock_read_count, are fetched and read into the memory.

In a nutshell, if the SQL statement serves more as a batch query rather than OLTP, FTS(FULL Table Scan) is actually faster than Index read.

The reverse is true in case of Online Transactions because your SQL statement is expected to return only a few rows.

Have a specific case to discuss, please leave a comment or reach me at munish07@gmail.com. I would be glad to answer.