If the table has primary keys then you can scan through the unique index. oracle index unique scan stops the scanning process once the first record is found. A unique index is created for the primary or unique key in the table. So, there are no duplicates with the same key. So there is no need to look for another rowid because there is only one record for the full table.
Table Index contains rowid then retrieve the row by rowid. Unique scans perform only if predicate referencing columns in a unique index. Also, the Where condition in the query should be an equal operator (Example: Once you want to fetch an employee document using primary keys in the query then execute a query like this. This is going to use a unique key index that is created using primary keys: WHERE emp_id=’900’ and emp_doc_no=’394829’)
If the data is not unique you are not allowed to create a unique index. Also, you never create a unique index but you create Unique Constraints that may create a unique index. This is the best information for the optimizer regarding the table has unique index. you can create a NON-UNIQUE index as well but then that is not a Unique index scan. NON-unique index may work as an Oracle index range scan. (Different Type of Create Index)
How to Find Record in Index Unique Scan.
How to find a record in a unique scan? I’m I thought the image could express this very nicely. Think about having a column called ‘Id’ as a primary key. That means you have a unique index for ‘Id’. If you want to query with the condition ‘Id=469’. The below diagram shows how to find a record using an index scan. ROWID is available in Leaf Blocks. You have to start from the ROOT Block and move to the leaf blocks.
There are different types of Index scanning methods according to the query filtration. Here let’s discuss the INDEX Unique 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
- Range Scan (Index Range Scan)
- Full Scan
- Fast Full Scan (Index Fast Full Scan)
- Skip Scan (Index Skip Scan)
How to Force Index Unique Scan in Oracle
This is simple. Most of our Index scan articles explain how to force an optimizer to use an index. It is the hint but that is not a good performance tuning habit, Because that can be the performance hit in one day. So best option is to give an Oracle optimizer to use a statistic weather scan through the index or do the full table scan. Below is how to add an index hint on the query.
-- SELECT /*+ INDEX(patients sex_index_ix1) */ name, height, weight FROM patient WHERE sex = 'M'; --
Oracle Index Unique Scan Example
For all our Oracle index scanning articles, we used the below dataset. Citizen table available columns and we pump millions of records by the below script.
--
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;
--
As you can see when I created the table I added citizen_id as a primary key for the table. Once I execute the above script there is a default index created as below. Now I like to execute the below query and let’s check the Oracle execution plan.
-- select * from citizen_table where citizen_id ='CT-55555' --
Oracle execution plan shows we fetch data by Oracle index unique scan. So there is a single record in the table and the optimizer takes rowid from the index and shows the full record. This is very fast compared to other scanning methods. Index name generated by Oracle. In this example, it’s ‘SYS_c006182940’.
Oracle Index Unique Scan with Wildcard(%)
This is extra information related to the wildcard operator. If you have a unique index on a table you can use the wildcard operator to fetch data with helping unique index. As an example, I like to execute the below query with the wildcard. You can see there is no full table scan on the table but there is an Oracle index range scan on table. That is also one tip that you can use as an Oracle performance tip on your programming. (10 Oracle Performance Tips)
Conclusion
Oracle unique index scan for unique key constraints in a table. Once you create the constraint oracle creates a unique index on a table. That means there are no duplicates in a table with the primary key column. So once the optimizer finds the required index from the unique index soon as stop the scanning and return the rowid. So Oracle’s unique index scan is done until finding a single record from the table.
Reference: Google Doc
