 |
|
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. |
