Optimize Full table scans tips

Answer:  If you cannot use an index to reduce total I/O, then a full table scan may be the optimal execution plan.  Given that Oracle must read every row of the table, there are only a few factors that will influence the speed of a full-table scan:

• Parallel Query:  Oracle parallel query is the best way to optimizer a full-table scan and a full-table scan on a server with 32 CPU's will run more than 30x faster than a non-parallelized full-table scan.  The trick when optimizing full table scans with parallel query is finding the optimal "degree" of parallelism.  See my notes on parallel query execution tuning and parallel query option tips. 
  
  
• Direct I/O:  If you can implement direct I/O you can improve the performance of full table cans.  See my notes on Oracle direct I/O.
  
  
• Table blocksize:  A table inside a 32k tablespace will read the data block slightly faster than a smaller blocksize tablespace.  See my notes of using larger blocksizes for full table scans.
  
  
• Multi-block reads (prior to Oracle 11g):  Multi-block reads speed-up full-table scan. Just as Oracle has implemented the initialization parameter db_file_multiblock_read_count for full-table scans (deprecated in 11g), Oracle allows this parameter to take effect when retrieving rows for a full-table scan. Since the whole index is accessed, Oracle allows multi-block reads.
  
• Lower the high-water mark:  An Oracle full-table scan read to the table high water mark, and may read lots of near empty blocks in cases where a table has experienced lots of "delete" activity.  In these cases, reorganize the table with the dbms_redefinition utility.

These optimization techniques are listed in order of full table scan optimization improvement.