Tuning SQL Table Access
Oracle Tips by Burleson Consulting
This chapter deals with the low-level table
access methods that result from running the execution plan for the
SQL statement. As you know from Chapter 3, any SQL statement may
have several execution plans depending on the CBO statistics, the
presence and types of indexes, and the optimizer_mode. At a
lower level, each execution plan translates into table access
methods. These low-level table access methods determine the overall
response time for the SQL statement, so it is imperative that you
understand the table access methods and how you can improve the
throughput for table access.
When discussing Oracle table access methods,
we must always remember that Oracle has only a few ways to access
any Oracle table, and that the physical access is always performed
by reading a database block. There are several ways to access Oracle
data blocks from SQL:
Full-table scan This can invoke
multi-block reads and parallelism to improve access speed.
Index access When using an
index to retrieve a row, the index provides Oracle with the ROWID,
which results in a request for a specific Oracle data block. We
also see index range scans, where a range of values is retrieved
from a table, and these accesses are based on a range of ROWIDs
that are collected while reading the index nodes.
Fast full-index scan This is a form of
index where Oracle reads all of the data blocks within an index
using multiblock reads and parallelism. It is the index equivalent
of a full-table scan.
To fully understand Oracle table access methods, you
must begin by carefully examining full-table scans. The SQL
optimizer manages index access within Oracle automatically, but we
will examine tricks for improving SQL index access in Chapter 20.
Within an individual table, we can perform numerous administrative
functions to improve the speed of SQL, including row resequencing
and changing the table storage parameters. This chapter will cover
the following table access issues:
SQL tuning and full-table scans
Table access via indexes
Changing table access methods
Resequencing table rows for reducing disk I/O
Oracle storage parameters and table access
Freelist management and table access
When you look at tuning any Oracle SQL
statement, you must first understand the ways that an Oracle table
can be accessed, and the internal block access techniques. Let’s
begin by examining the full-table scan.
SQL Tuning and Full-Table Scans
There are some important rules for when a SQL
query should perform a full-table scan. Unnecessary large-table
full-table scans cause a huge amount of unnecessary I/O and can drag
down an entire database. The tuning expert first evaluates the SQL
in terms of the number of rows returned by the query. If the query
returns less than 40 percent of the table rows in an ordered table,
or 7 percent of the rows in an unordered table, the query can be
tuned to use an index in lieu of the full-table scan. The most
common tuning for unnecessary full-table scans is adding indexes.
Standard B-tree indexes can be added to tables, and bitmapped and
function-based indexes can also eliminate full-table scans. The
decision about removing a full-table scan should be based on a
careful examination of the I/O costs of the index scan versus the
costs of the full-table scan, factoring in the multi-block reads and
possible parallel execution. In some cases an unnecessary full-table
scan can be forced to use an index by adding an index hint to the
In cases where a full-table scan is the
fastest access method, the tuning professional should ensure that a
dedicated data buffer is available for the rows. In Oracle7, you can
issue alter table xxx cache. In Oracle8 and beyond, the small
table can be cached by forcing the table into the KEEP pool.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.