How to Oracle Table Access by Index Rowid

oracle table access by index rowid
Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print

Before explaining Oracle table access by index rowid, I would like to explain a bit about what is rowid and how it is used in the Oracle database. The table is the basic data organization method in Oracle. Data or rows are stored in the data blocks.

Every table contains rows, and those rows need some identifier. Therefore, oracle uses ROWID as a unique identifier and it is used to find the physical address of the ROW. ROWID is a 10 BYTE physical address of a row.  All the rowid pointing with a specific file, block, and row number.

A ROWID (Extended ROWID) contains below 4 components:

  • –    DataObject Number
  • –    Datafile Number Relative to the Tablespace
  • –    DataBlock Number (inside the DataFile)   
  • –    RowNumber inside the DataBlock
oracle table access by index rowid

Let’s take an example rowid like AAAT1+ACOAAAKs7AAL. AAL is the row number. Oracle There is a row directory in the database. The row number is the index entry number of the row directory. Row directory entry pointing with a position of the row in the block.

When creating an index for a table rowid is used to fetch records from a table. When an index is created as a B-TREE index (There are other types of indexes like Domain, Bitmap, function-based, etc.) all the entries in the index contain a rowid. It is pointing to the address of the table row. It is the fastest way to access table records by enabling a single IO.

Oracle Table Access by Index Rowid

In most cases table access using rowid once after scanning a index. Sometimes index contains all the information that the query needs. In such cases, there is no need to access a table using rowid. Oracle has an index scan method. All the types take rowids or one rowid according to the scan type listed in below. Finally, rowid is used to fetch records from a table with a single IO.

Index Lookups

If I summarize how Oracle table access by index rowid there are multiple steps. In most cases, Oracle does the following

  • Obtain index rowids that match with the WHERE condition.
  • If there are multiple rowids then batch or multiple OI.
  • Find the location using ROWID.
  • Fetch the location and retrieve the record.

Oracle Table Access by Index Rowid Example

Let’s take our usual example with a sample script. The citizen table contains records related to the country’s population. Let’s create an index for citizen_nic  now I’m going to retrieve data from a table using the below query.

--
CREATE TABLE citizen_table(
    citizen_id VARCHAR2(50) ,
    citizen_nic VARCHAR2(50) ,
    citizen_tax NUMBER ,
    citizen_name VARCHAR2(100) ,
    PRIMARY KEY(citizen_id)
);
declare 
  -- insert 1000000 records into database
  i integer:=0;
begin
LOOP
   i:= i+1;
     INSERT INTO citizen_table (citizen_id, citizen_nic, citizen_tax, citizen_name) 
   VALUES ('CT-'||i,1000000+i, round(dbms_random.value(100,100000),0), 'CT-name'||i);
   EXIT WHEN i > 999999; END LOOP;
end;
--

CREATE INDEX citizen_nic_ix1 ON citizen_table (citizen_nic);
--
select
   citizen_name
from
   citizen_table
where
   citizen_nic = '1000099';
--

Let’s see how Oracle optimizer finds the required record from the table. The easiest way is to Explain Plan. Below is the Explain plan for the above query and you can clearly see Oracle uses ROWID to fetch records from the table.

oracle table access by index rowid

Oracle uses an INDEX RANGE SCAN to filter required records and then Oracle TABLE ACCESS BY INDEX ROWID. Here there is a BATCH call and there is a single IO call. Hope this example easy to understand how Oracle doing this.

In most cases, oracle table access by index rowid is very fast. But it is only for a smaller number of records. If the query fetches a small number of records, then this is fast. Otherwise, there are a large number of records to fetch so a full table scan might perform well.

We discussed what is rowid and how to fetch records from a table using rowid. Below is the summary of the content and how the Oracle table can be accessed by index row in different types of scans.

oracle table access by index rowid

Summary of Oracle table access by index rowid

  • Rowid is a unique ID for each row in a table.
  • ROWID is the fastest way to find the physical record in the database but not directly give a physical address.
  • ROWID is stored in an index when scanned through an index
OperationOptionDescription
INDEX (These are access methods.)UNIQUE SCANUnique scan fetches only a single row and single rowid from the index.
INDEXRANGE SCANThis is also the same with FULL SCAN but rowid values are in descending order.
INDEXRANGE SCAN DESCENDINGThis is fetched multiple rowids similar to RANGE SCAN but it’s in descending order.
INDEXFULL SCANThis is fetch multiple rowids similar to RANGE SCAN but it’s in descending order.
INDEXFULL SCAN DESCENDINGThis is also same with FULL SCAN but rowid values are in descending order.
INDEXFAST FULL SCANCompared to FULL SCAN this is much faster. Because rowids fetch using multi-block reads. There is no order just scan everything in parallelly. Use with cost-based optimizer.
INDEXSKIP SCANIndex use to save low cardinal columns and finally filter some rowids according to the algorithm. Anyway, filtered rowids are used to fetch records from a table.
INDEXDOMAIN INDEXDomain index also returns multiple rows. The way of saving index values is different but rowid map with all the indexes in structure.
INDEXBITMAP INDEXCompared to FULL SCAN this is much faster. Because rowids fetch using multi-block reads. There is no order just scan everything in parallelly. Use with cost-based optimizer.
All the examples are how the Oracle table is accessed by index rowid. There are over 20 ways to create an index in Oracle. Rowid is used to map data records from tables and indexes. Rowid format helps to identify the correct location of the record. Most of the Oracle performance tuning happens with the index.  

Ref: oracle doc

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print

Leave a Comment

Your email address will not be published. Required fields are marked *

Related Articles
You May Like
Subscribe to our Newsletter
Scroll to Top