Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Evaluating the Legitimacy of a Full-Table Scan
Oracle Tips by Burleson
 

When making the decision to change a full-table scan into an index range scan, the primary concern is the speed of the query. In some cases the full-table scan may have more physical disk I/Os, but the full-table scan will be faster because of a high degree of parallelism.

In other cases, you need to consider the number of rows retrieved as a function of the clustering of the rows in the table. For example, if your table is clustered or you have manually resequenced the rows into primary-key order, a great many adjacent rows can be read in a single I/O and an index range scan will be faster than a full-table scan for up to 40 percent of the table rows. On the other hand, if your table is totally unordered a request for 10 percent of the table rows may cause the majority of the table data blocks to be read. Of course, you also need to consider the db_block_size, the degree of parallelism on the table and the setting for db_file_multi_block_read_count init.ora parameter. Hence, the general guideline for replacing a full-table scan with an index range scan is as follows:

  • For row-sequenced tables Queries that retrieve less than 40 percent of the table rows should use an index range scan. Conversely, queries that read more than 40 percent of the data blocks should use a full-table scan.

  • For un-ordered tables Queries that retrieve less than 7 percent of the table data blocks should use an index range scan. Conversely, queries that read more than 7 percent of the table data blocks will probably be faster with a full-table scan.

While these general guidelines help, it is always a good idea to test the execution speed in SQL*Plus by issuing the set timing on command. There is no substitute for experimentation. Even the most experienced SQL tuner must verify their execution plan by timing the query execution in SQL*Plus.

Remember to always make sure that the full table cannot be improved with index access. Each full-table scan SQL query should be evaluated, based upon the number of rows returned by the query. Full-table scans can be removed by the following methods:

  • Adding a B-tree index

  • Adding a bitmapped index

  • Adding a function-based index

  • Forcing the CBO to use an index with an index hint

Now that we have verified the legitimacy of a full-table scan, we are faced with invoking Oracle parallel query. To understand the issues, let’s take a closer look at the internal operations of parallelism in Oracle.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter