Oracle offers a variety of indexing methods. index range scan oracle is one of the index scanning methods to gather ROWIDs and retrieve data from the table. There are a lot of index varieties like B-tree, Domain, Bitmap, Function-based index, and so on. See our Create Index Guide
Regardless of those index structures oracle needs ROWIDs to fetch data from a table. There are different types of Index scanning methods according to the query filtration. Here let’s discuss the INDEX RANGE SCAN but there are different index lookup methods
Index Lookups
- Unique Scan
- Range Scan
- Full Scan
- Fast Full Scan
- Skip Scan
ROWID is the identification of the row in a table. There are billions of records in a table but never ever use all the records in a single time in a query. When filtering the data by query Oracle optimizer uses one of the above methods to fetch ROWIDs from the index. It’s up to Oracle Optimizer to select which type of scanning method to use and whether to use index or table scan to fetch required data from the database.
What is an index range scan in Oracle?
Hearing the name you can imagine this is going to scan the range of the index. The index range scan starts from the root block. Then Travers leaf blocks until find the relevant data.
I’m not going to explain how the B-tree index works, but by default, Oracle creates a B-tree index. B-Tree is a Balance Tree So values are placed in balanced order in Oracle. That is good on high cardinality columns. Then Oracle traverses branches to find the leaf block. Once found the correct leaf block contains the required ROWID for physical location.
Leaf nodes contain a doubly linked list so traversing through left and right is easy. Therefore no need to come from ROOT every time. If the query searches for a range of values in the where condition What happens in the index range scan oracle? first looks for the highest value or lowest value leaf nod in the b-tree according to the where condition, then traverses using a linked list and stops once comes into the next condition.
We discuss Full Examples at the End for now consider the below examples
EG: where y between 20 and 30;
Oracle Comes into the ROOT block and moves to branches and reads. Then identify the first leaf not corresponding to values 20; Then read only the leaf node or another leaf node if needed until encounter key value 30 and then stop. Now you have required ROWIDs between 20 and 30.
Then go to the table and fetch required values for ROWIDs. That can happen onetime process or multiple times according to the dataset. it depends on the optimizer; it depends on the explained plan and the behavior of the query.
Index Range Scan Examples
This is a simple example of how to use index range scan oracle. Let’s get a Citizen table available with columns NIC number (National Identity card), Annual Tax, Name, and Id.
--
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;
--We added 1000000 records as a considerable amount of data into the table We can write queries to check the index range scan Oracle now. But still, we don’t have an index in the table. Let’s create an index first. I’m going to create two indexes for this example. One for citizen_nic and the other one for citizen_tax. how to create an index lets see.
-- CREATE INDEX citizen_nic_ix1 ON citizen_table (citizen_nic); CREATE INDEX citizen_tax _ix1 ON citizen_table (citizen_tax); --
Index Range Scan Oracle: Example 1
In the first example, I want to filter a single record for citizen_nic = 1000099. Before adding the index and after adding the index you can see the difference between the cost and the Index used to fetch the data.
-- select citizen_name from citizen_table where citizen_nic = 1000099; --
Index Range Scan Oracle: Example 2
In the second example, I’m going to fetch a range of data. In this example, I’m going to filter the range of taxes paid by citizens.
Index Range Scan Performance
If you are using equal or range operations (e.g. > < >= <= between ) or where the data to be returned is not unique then performance improves if you accurately use index and query. Oracle optimizer decides whether to use an index or not. If you have a lot of data in the table most probably index will be used. Otherwise, a full table scan may speed up.
Index Range Scan Hint
You can use optimizer hints like below but it does not say all the time Oracle optimizer going to use an index it’s free for to optimizer to use or not. We can just suggest we have an index to use. You can have a look at the execution plan for whether the index is used or not for the query.
-- select /*+ INDEX(table_name index_name) */ from table etc.. --
Index Range Scan Descending
INDEX RANGE SCAN DESCENDING is used when you have a query in ASC or DESC order. In my example I created an index in DESC order so when I execute the query below you can see results are in descending order.
-- select citizen_name from citizen_table where citizen_tax between 300 and 3000 order by citizen_tax; --
Conclusion
According to the examples clearly, we can see there is an improvement in execution time and cost and so on. Once the index is used for the query first find the ROWIDs for the records and then fetch data from a table. TABLE ACCESS BY INDEX ROWID saying real data fetched from table.
