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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Other Tuning Tips
Oracle Tips by Burleson

This section discusses some situations that commonly require performance tuning, including several stumbling blocks that confuse new PL/SQL developers. One of the most common pitfalls by new developers is scanning too many records (often whole tables of records).

Full-Table Scans

Oracle uses a full-table scan of a table when it cannot use any of the indexes for the table. A full-table scan retrieves every row of data from a table. In large tables, this process can take a long time to execute and can considerably diminish performance of your applications.

If you haven’t quite grasped the inefficiency of using a full-table scan, try imagining that you’re running a video store and someone returns a movie. In order to make the returned movie accessible for other customers to rent, it has to be put back in the proper place.

If you have to walk around your store and check all the movies to find the proper place, you’re performing the equivalent of a full-table scan. To avoid full-table scans in your DML commands, you should not:

  • Compare the values of two columns within the same table.

  • Pass any columns to a predefined or a stored function.

  • Use the IS NULL and IS NOT NULL comparisons against any column.

  • Use NOT IN comparisons against any column.

  • Use the LIKE operator against any column.

  • Use subqueries against non-index columns.

  • Make comparisons against non-indexed columns.

There are several instances in which a full-table scan is as quick (or quicker) than the use of indexes. These occurrences include the following:

  • A DML statement must return more than 20 percent of the rows in a table.

  • The functionality of a statement requires that every row of a table will be processed.

  • The table is extremely small. It’s difficult to put an exact size on a table, but if a table has more than 500-1,000 rows, a full-table scan will probably be less effective than an indexed reference to the table. You’ll need to do some ad hoc testing to determine which approach is best.

A related performance problem occurs when the WHERE clause of a statement is incomplete, causing Oracle to scan too many rows of data. While this isn’t as expensive as performing an unnecessary full-table scan, it still requires Oracle to waste resources.

Going back to the returned movied metaphor, knowing that the newly returned movie goes in the “Adventure” section is better than knowing only that it belongs somewhere in the store. However, you’ll be able to replace the film much more quickly if you also happen to know the name of the movie.

In a SELECT statement, the category and name of the movie would be included in the WHERE clause.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


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