Question: I have a SQL with a
legitimate full-table scan and I want to optimizer my full
table scan. How do I optimize a full table scans
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
- 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
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
- 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
These optimization techniques are listed in order of full
table scan optimization improvement.
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.