Oracle SQL Tutorial - How To Make Your SQL Lightning-Fast

Tip #5 - Use Full-Table Scans in your SQL

As mentioned in tip #4 of this Oracle SQL tutorial, there are situations in which the use of an index may cause not just your particular query to perform poorly, but the whole database as well.

How can this happen ? Here's a brief explanation ...

When data is read form disk the blocks are stored in memory in what's called the database buffer cache in case there wanted by another query. If there aren't any free blocks in the buffer cache then Oracle has to overwrite some of the old ones with the new data.

How does the Oracle database know which blocks to overwrite ? Simple, there is another data structure called the least recently used list which has a head and a tail. When blocks have to be overwritten because there are no unused block, Oracle starts overwriting blocks from the least recently used end of the list.

Obviously if a very large number of blocks are read from the disk, eventually more recently used blocks are going to be overwritten. This means that if the table is very large, reading a large number of blocks via an index in the SQL statement could remove everything else from the database buffer cache .

This in turn means that the performance of other queries will be degraded because they will be forced to do a physical read from disk to retrieve the data they need rather than being able to perform a logical read from the buffer cache.

On the other hand, when the data is read by a full-table scan, only the last few blocks read are kept in the buffer cache as they are always added to the least-recently-used end of the queue. This means other queries are unaffected and therefore run much faster due to the fact that they only have to read from memory rather than the disk.

Full table scans can also be faster than index reads when the table is very small, because you may end up reading more blocks by reading the index and then reading the table than by reading the whole table.

One solution might be to index all the columns so that reading the index alone would satisfy the needs of the query. However if the table is very volatile, more time will be spent keeping the indexes up to date than will be saved by using the indexes.

Other solutions are to cache the table in memory, by storing it in a PL/SQL collection (if it's needed by only one user) or caching the table by using a query hint or when the table is created. The final alternative is to use an index-organised table (iot).

The next part of this Oracle SQL tutorial explains how to optimise join queries for peak performance.

Return to Oracle SQL Tutorial part 1