How to work Oracle Table Access by Index Rowid

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

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.

I repeat Rowid is the fastest way of retrieving a single row. rowid specifies the data file and the data block contains the row including the location of the row inside the block. The index also contains the rowid and those rowid maps with table records. When fetching a small number of records using a query Rowid’s in an index is fast And a full table scan performs well on fetching a large amount of records.

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

• Unique Scan (Unique Scan)
• Range Scan (Index Range Scan)
• Full Scan
• Fast Full Scan (Index Fast Full Scan)
• Skip Scan (Index Skip Scan)

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

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

Ref: oracle doc