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