An Oracle index full scan is Oracle start read block from ROOT and then navigating to the down branch until find the leaf block on the left side. Move to the left side of the scan doing the ascending.
Otherwise scanning on descending then it is starts from the right side. In this scan, the oracle reads all the blocks one by one in a sequential manner. Once meet the first leaf block then read the entire bottom blocks horizontally one by one.
As we discussed in Oracle fast full scan reads the block using a Multiblock IO operation. Because in that case query does not look for an order. So parrel process happens when collecting rows. But in this case, Oracle doing SINGLE BLOCK IO. For collecting rowids, that means returning data in an order.
There are different types of Index scanning methods according to the query filtration. Here let’s discuss the INDEX FULL SCAN but there are different index lookup methods. ROWID is the identification of the row in a table. Regardless of those index structures(Bitmap, Domain, Function-Based, etc.), oracle needs ROWIDs to fetch data from a table.
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)
Why Optimizer Choose the Oracle Index Full Scan
Query needs data in an Order defined by ORDER BY clause also if there are no specific data like in an Index Unique Scan. Also, there is not any RANGE according to one column like in an Index RANGE scan. But we have Some orders therefore we can use Index Fast Full Scan. Then Oracle Optimizer Chooses Oracle index full scan and returns all the data in an Order.
Some additional conditions can apply for the Index Full Scans.
- All the columns in a query that looks for fetch from a table that columns need to be in the index as well.
- At least a single column of the index should not be null (This is a bit unclear Sometimes Some are saying this is not mandatory for a Full scan in the index)
- A query needs to include the ORDER BY
If all the possibilities can happen the Optimizer has an option to choose Index full scan. But that is also not guaranteed because statistics can say TABLE full scan may be faster than this.
Follow Some Important Performance Improvement Tips with ORDER BY Ultimate Guide To Oracle Order By Performance 10 Tips, Oracle ORDER BY Index | How to Perform? | 6 Examples
How Oracle Index Full Scans Executes
I started the article with a brief explanation of how to work full scan in Oracle for indexes. We create an index looking for some gain on queries but having database-poor habits may drop performance IF you are familiar with how Oracle uses indexes that help when writing queries.
Let’s discuss broadly How it works. As you can see in the image If the above conditions are met First start from the ROOT Block and go to the leftmost block when it is looking for ascending order. Then Not coming again to the TOP or UP side. Just move to the right side through the Leaf Block.
Scan one block at a time using SINGLE BLOCK IO. In this example, the diagram returns Order by Column 1.
Also if you are happy look at some additional performance guides on How to Performance Tuning in Oracle | 10 Tips
Oracle Index Full Scan Example
We already discussed how Oracle does things but let’s have a look at an example now. first, I would like to add records into a table called Citizen_table. Below is the sample script to create a table and add mock data. In this example, there are over a million records.
--
CREATE TABLE citizen_table(
citizen_id VARCHAR2(50) ,
citizen_nic VARCHAR2(50) ,
citizen_tax NUMBER ,
citizen_city VARCHAR2(50),
citizen_name VARCHAR2(100) ,
PRIMARY KEY(citizen_id)
);
declare
-- insert 1000000 records into database
i integer:=0;
type namesarray IS VARRAY(10) OF VARCHAR2(50);
names namesarray;
begin
names := namesarray('City_1', 'City_2', 'City_3', ' City_4', 'City_5','City_6',' City_7','City_8','City_9','City_10');
LOOP
i:= i+1;
INSERT INTO citizen_table (citizen_id, citizen_nic, citizen_tax,citizen_city, citizen_name)
VALUES ('CT-'||i,1000000+i, round(dbms_random.value(100,100000),0), names(round(dbms_random.value(1,10),0)),'CT-name'||i);
EXIT WHEN i > 999999; END LOOP;
end;
--
Now I would like to execute a query with an index and before let’s run the same without an index. Hoping the table full scan without an index. There are not many columns included. I want citizens according to the citizen name in ascending order.
-- select citizen_id, citizen_name from citizen_table order by citizen_name; --
As you can see there is a full table scan without an index but once after creating the index below, I hope there is an index full scan on the table.
-- CREATE INDEX citizen_rel_ix3 ON citizen_table (citizen_id,citizen_name); --
Once the fetch rowID is from the index, we need to fetch the record using ROWID if all the columns are included in the index there is no need for another IO call to fetch data from a table.
Conclusion
Oracle index full scan is used to fetch data when there is an order by clause. If the query doesn’t have a filter according to the specific range, then all the data must be fetched. In such cases, Oracle uses this scan. The first oracle comes to the root and reaches the first leaf block through the branch block if it exists. Then horizontally traverse through the entire leaf block in an order.
There are no multi-block operations. Time may be greater than a fast full scan. This is comparatively getting more time than unique index scans or range scans because reading the entire index in order
Ref: Oracle DOC
