Four types of Oracle index Scanning

A single ROWID is often returned for finding a value through a UNIQUE index. If the UNIQUE or PRIMARYKEY constraint exists (it ensures that the statement only accesses a single row), Oracle often implements uniqueness.

A single ROWID is often returned for finding a value through a UNIQUE index. If the UNIQUE or primary key constraint exists (it ensures that the statement only accesses a single row), Oracle often implements uniqueness.

There are four types of Oracle index scans based on different index types and where restrictions: 3, 4, which can be normalized.
(1) unique index scan (index uniquescan)
(2) index range scan)
(3) full index scan)
(4) index fast full scan)
(5) index skip SCAN (indexskip scan)
1. unique index scan)
A single ROWID is often returned for finding a value through a UNIQUE index. If the UNIQUE or primary key constraint exists (it ensures that the statement only accesses a single row), Oracle often implements a UNIQUE scan.
1. Data must be accessed through the UNIQUE index [UNIQUE;
2. When accessing data through a unique index, the number of records returned each time must be 1;
3. The WHERE clause must use the equivalent [=] condition to filter data:
Note: a field in the table is unique. If the INDEX on the field is not unique, the CBO selects the path of index range scan to access data. This is true in real scenarios, because some systems use applications to ensure that the fields in the table are unique ,, the unique constraint or unique index is not created on the corresponding fields of the table to ensure data uniqueness. In this case, the CBO may have deviations when selecting an execution plan. Therefore, in this case, it is better to create a unique index on the table even if the application can ensure the uniqueness of the data.
Example:
SQL> create table t as select * from dba_objects where object_id is not null;
The table has been created.
SQL> alter table t modify (object_id not null );
The table has been changed.
SQL> create unique index index_t on t (object_id );
The index has been created.
SQL> select * from t a where a. object_id = 75780;
Execution Plan
----------------------------------------------------------
Plan hash value: 4119349871

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Tim

--------------------------------------------------------------------------------

| 0 | select statement | 1 | 207 | 2 (0) | 00:

| 1 | table access by index rowid | T | 1 | 207 | 2 (0) | 00:

| * 2 | index unique scan | INDEX_T | 1 | 1 (0) | 00:

-------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

2-access ("A". "OBJECT_ID" = 75780)

Statistics
----------------------------------------------------------
6 recursive cballs
0 db block gets
12 consistent gets
1 physical reads
0 redo size
1348 bytes sent via SQL * Net to client
404 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Related reading:

Introduction to Oracle Virtual Index

Check whether indexes need to be reconstructed in Oracle

Oracle case when index null index bitmap Index

Oracle analysis table and Index

Automatic Indexing of Oracle primary key constraints