BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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 scanThis method reads every row in the table, sequentially accessing every data block up to the high water mark.

  • Hash retrievalA symbolic hash key is used to generate the ROWID for rows in a table with a matching hash value.

  • ROWID accessThis 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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter