Oracle Table Access Methods
Oracle Tips by Burleson Consulting
When fetching rows from a table, Oracle has
several options. Each of these access methods obtains rows from a
table, but they do it in very different ways:
Full-table scan This
method reads every row in the table, sequentially accessing every
data block up to the high water mark.
Hash retrieval A
symbolic hash key is used to generate the ROWID for rows in a
table with a matching hash value.
ROWID access This
access selects a single row in a table by specifying its ROWID.
The ROWID specifies the block number and the offset of the row in
the data block. This is the fastest method for accessing a row,
and it is commonly seen in execution plans where Oracle has
retrieved a ROWID from an index and uses the ROWID to fetch the
Letís take a closer look at these table access
In Oracle, a full-table scan is
performed by reading all of the table rows, block by block, until
the high-water mark for the table is reached (see Figure 3-2). As a
general rule, full-table scans should be avoided unless the SQL
query requires a majority of the rows in the table. However, this
issue is clouded when you are using Oracle parallel query or when
the db_file_multiblock_read_count is used on a database
server with multiple CPUs. I will go into great detail on this issue
in Chapter 10, but for now letís just look at the situations where
the SQL optimizer chooses a full-table scan.
Figure 2: A
full-table scan with parallel query and multiblock reads
Any one of the following conditions will
cause Oracle to invoke a full-table scan:
When no indexes exist for the table
When a query does not contain a where clause
When an indexed column is invalidated by placing it inside a BIF
When a query uses the like operator, and it begins with a
With the cost-based optimizer, when a table
contains a small number of rows
When the optimizer_mode=all_rows in the initialization file
Warning: It is important to note that
Oracle will always perform a full-table scan up to the high-water
mark for the table. This behavior can cause excessive response times
in cases where a significant number of rows have been deleted from a
table. For example, in a table with 100 blocks of data that has had
90 blocks of rows deleted, full-table scans will continue to read
100 blocks. The remedy for this problem is to reorganize the table.
Next, letís look at hash access.
implements hash storage through single-table clusters and multiple
table clusters. In a multiple table cluster, the hash is used to
reduce I/O during join operations. When frequently joined tables are
placed in a hash cluster, rows from both tables are placed in the
same data block, such that a SQL join will need to fetch fewer rows.
Access with a hash is based on a symbolic key. The symbolic key is
fed into the hashing algorithm that is programmed to quickly
generate a hash value that is used to determine the data block where
the row will reside, as shown in Figure 3-3. Because of the risk of
relocating rows, hash access should only be used in static tables.
Figure 3: Access with
Hash row access should not be confused with a
hash join in SQL. In a hash join, one table is accessed via a
full-table scan, and a hash table is built in-memory from the result
set. This hash table is then used to access the rows in the second
Access by ROWID is the fastest way to get a
single row. As you may already know, the ROWID for a row contains
the data block number and the offset of the row in the block. Since
all of the information required to fetch the data block is contain
in the ROWID, the ROWID method can very quickly retrieve a row, as
shown in Figure 3-4. In practice, select by ROWID is generally done
when a ROWID is gathered from an index, and the ROWID is used to
fetch the row. You may also see ROWID access when the row is
re-retrieved inside an application program since the program
acquired and stored the ROWID.
Figure 4: Access by
Next, letís take a quick look at index access
methods. As you know, Oracle often uses indexes to gather row
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.