Oracle optimizer can sometimes trick you and this post is about a finding that clearly demonstrates that. Putting it shortly, while the execution plan shows that the optimizer is doing “INDEX RANGE SCANE”, in actuality it is going with “INDEX UNIQUE SCAN”. Here is a test-case in Oracle 19c.
First, lets create a table, populate it with data and add index on its column.
SQL> create table table1(id integer);
Table created.
SQL> insert into table1 select object_id from dba_objects;
7433 rows created.
SQL> create index IND on table1(id);
Index created.
Let’s confirm the type of the index just created which will be needed a little later
SQL> select UNIQUENESS from dba_indexes where index_name = 'IND';
UNIQUENES
---------
NONUNIQUE
Since the index is nonunique, the optimizer will (or should !?) never go with “INDEX UNIQUE SCAN”.
Let’s now add a primary key.
SQL> alter table table1 add primary key(id);
Table altered.
Since an index on the column ID already exists, Oracle doesn’t attempt to create a new one. It will link the constraint to the existing one. We can confirm it by querying DBA_CONSTRAINTS view. Additionally, the database does not update the index type in the data dictionary.
SQL> select constraint_name, index_name from dba_constraints where table_name = 'TABLE1';
CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_C008559 IND
SQL> select UNIQUENESS from dba_indexes where index_name = 'IND';
UNIQUENES
---------
NONUNIQUE
What we have so far is that the table has a primary key constraint enforced via an index which Oracle reports as NONUNIQUE (which effectively is not so).
It is interesting to see what index access path will be taken by the optimizer for a query with equality predicate.
select * from table1 where id = 2;
============
Plan Table
============
------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1 | |
| 1 | INDEX RANGE SCAN | IND | 1 | 13 | 0 | |
------------------------------------+-----------------------------------+
The optimizer reports “INDEX RANGE SCAN”. According to the type of the index in the database dictionary this is expected. However, we definitely know that the index is unique since this is what a primary key constraint is enforced by and unique index should not be causing range scans.
Let’s see what session statistics related to indexes the database reports.
select s.name, value from v$mystat m, v$statname s where s.STATISTIC# = m.STATISTIC#
and s.name like '%index%'
and value != 0;
NAME VALUE
--------------------------- ----------
index range scans 1
So, the statistics also state that the optimizer chooses the “INDEX RANGE SCAN” index access method (this statistics is missing in the Database Reference 19c).
Finally, let’s take a look at the optimizer trace (Access path analysis section only) to see what really happens behind the scenes.
Access Path: TableScan
Cost: 2.000631 Resp: 2.000631 Degree: 0
Cost_io: 2.000000 Cost_cpu: 23521
Resp_io: 2.000000 Resp_cpu: 23521
Access Path: index (index (FFS))
Index: IND
resc_io: 2.000000 resc_cpu: 0
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 2.000000 Resp: 2.000000 Degree: 1
Cost_io: 2.000000 Cost_cpu: 0
Resp_io: 2.000000 Resp_cpu: 0
****** Costing Index IND
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Access Path: index (UniqueScan)
Index: IND
resc_io: 0.000000 resc_cpu: 1050
ix_sel: 0.004000 ix_sel_with_filters: 0.004000
Cost: 0.000028 Resp: 0.000028 Degree: 1
****** Costing Index IND
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Access Path: index (AllEqUnique)
Index: IND
resc_io: 0.000000 resc_cpu: 1050
ix_sel: 0.004000 ix_sel_with_filters: 0.004000
Cost: 0.000028 Resp: 0.000028 Degree: 1
One row Card: 1.000000
Best:: AccessPath: IndexUnique
Index: IND
Cost: 0.000028 Degree: 1 Resp: 0.000028 Card: 1.000000 Bytes: 0.000000
Whola ! In actuality, no “INDEX RANGE SCAN” happens but “INDEX UNIQUE SCAN”. This is confirmed by the access path choice the optimizer ends up with: Best:: AccessPath: IndexUnique (line 30). The “INDEX RANGE SCAN” access path is not even considered as an option. Apparently, the session statistics gets the data from what the optimizer shows up in the execution plan.
In summary, while the optimizer reports “INDEX RANGE SCAN” access method it may not actually be doing so but instead “INDEX UNIQUE SCAN”. The execution plan is not the source of truth in such a case.