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

Let’s take a closer look at these table access methods.

Full-Table Scan

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.

Hash Access

Oracle 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 a hash

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

ROWID Access

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 ROWID

Next, let’s take a quick look at index access methods. As you know, Oracle often uses indexes to gather row information.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.