Oracle quickly fetches data by using an Index. There are different scan methods including Oracle index skip scan. If you have a composite index in the table, then this is skipping the leading column for fetching data.
As an example, if you have 3 columns index as column1, column2, column3 But where condition available column2 and column3 then skip scan used to fetch data by ignoring column1 in index.
Before Oracle 9i there was a restriction for it is mandatory to have column1 in where condition filter but now it is removed and skip scan is used in such a case when it is better than full table scan in the table. Optimizer uses the database statistics to decide whether to use full table scan, skip scan, or any other scanning method. Check the complete example below.
There are different types of Index scanning methods according to the query filtration. Here let’s discuss the INDEX SKIP SCAN but there are different index lookup methods. ROWID is the identification of the row in a table. Regardless of those index structures 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
As we discussed earlier query must have and match those columns with the index to scan the index when filtering but after introducing the Oracle index skip scan the query can bypass the leading edge. After that able to access the multi-column index without a full table scan. This is not as fast as an Index range scan in Oracle but it may be faster than a full table scan.
Advantages of Oracle Index Skip Scan
- Index maintenance is also the cost of the database. Reducing the number of indexes helps to maintain many indexes for a single table. There are no limitations on which type of index to create on the table. You are allowed to use any index type like Domain index, Bitmap index, Function base index, or any other type of index as your best index according to your query.
- If there are a lot of indexes in a table and a lot of data available in the table CRUD operation takes much more time. Because every single insert, update, or delete index needs to be updated and refreshed according to the new operation. That can be hit on performance sometimes if you have a lot of indexes in a single table. Indexes fetch data quickly, but balancing indexes is helpful in CRUP operations.
Comprehensive Example of Index Skip Scan Oracle
This is a simple example of a Country’s population. This is the example we had all Oracle index scanning types. Let’s take the Citizen table and put 1 million records into the table. Now I want to filter citizen_nic =’1000349′ in this table. Below is the sample code I used.
--
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;
--
select *
from citizen_table
where citizen_nic ='1000349'
What happened I ran the above query without any index. Then there should be a full table scan to filter my record. This is an ideal index created using column citizen_nic but think about I don’t have an index only for column citizen_nic but I have an index like below.
-- CREATE INDEX citizen_rel_ix1 ON citizen_table (citizen_city, citizen_nic, citizen_tax); --
What is Oracle Index Skip Scan?
According to my reading and learnings if Oracle optimizer decides to use index skip scan then how to use our index citizen_rel_ix1. Oracle generates internal queries for such a situation. In my case have to generate 10 different subqueries like below. It is because otherwise index not be able to be used.
-- Select * from citizen_table Where citizen_city='City_1' and citizen_nic ='1000349' union Select * from citizen_table Where citizen_city='City_2' and citizen_nic ='1000349' union Select * from citizen_table Where citizen_city='City_3' and citizen_nic ='1000349' union Select * from citizen_table Where citizen_city='City_4' and citizen_nic ='1000349' union Select * from citizen_table .. .. .. .. Where citizen_city='City_9' and citizen_nic ='1000349' union Select * from citizen_table Where citizen_city='City_10' and citizen_nic ='1000349' --
This process can take more time compared to direct Oracle Range Scan. But this can take less time compared to a full table scan. But it depends on statistics in the database. If the Oracle index skip scan is used correctly then there is an execution plan like below.
--
SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=15)
0 SORT (AGGREGATE)
1 INDEX (SKIP SCAN) OF 'citizen_rel_ix1' (NON-UNIQUE)
--Oracle Index Skip Scan Hints
If you want to force the database to use index but also force the database to do the index skip scan, then you can add a hint on the query like below. Hint is not promoted every time because sometimes it may be a performance issue.
Using hints in every place is Oracle’s performance bad habit. Oracle uses cost-based optimizers and statistics to perform the best way to handle a query.
-- Select /*+ index_ss(citizen_table i_ct)*/ * from citizen_table Where citizen_city='City_4' and citizen_nic ='1000349' --
Conclusion
oracle index skip scan is one of the index scanning methods that use the existing index to fetch data by skipping the first column of the index. This is possible on composite index and query where condition filter data from the second column.
In that kind of situation Oracle decides to use the existing index or oracle does the full table scan according to the statistics.
Reference
