I have created a cluster and a table in the cluster with the following definitions:
create cluster roald_dahl_titles (
title varchar2(100)
);
create index i_roald_dahl_titles
on cluster roald_dahl_titles
;
create table ROALD_DAHL_NOVELS (
title varchar2(100),
published_year number
)
cluster roald_dahl_titles (title)
;
Notably, this is index is not created with the unique constraint, and it's quite possible to insert duplicate values into the table ROALD_DAHL_NOVELS:
insert into roald_dahl_novels (title, published_year) values ('Esio Trot', 1990);
insert into roald_dahl_novels (title, published_year) values ('Esio Trot', 1990);
I then gather statistics on the both the table and the index, and look at an execution plan that uses the index:
begin
dbms_stats.gather_table_stats(user, 'ROALD_DAHL_NOVELS');
dbms_stats.gather_INDEX_stats(user, 'I_ROALD_DAHL_TITLES');
end;
/
explain plan for
select published_year
from roald_dahl_novels
where title = 'Esio Trot';
select *
from table(dbms_xplan.display(format => 'ALL'));
The contents of the execution plan I find a bit confusing, though:
Plan hash value: 2187850431
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 28 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS CLUSTER| ROALD_DAHL_NOVELS | 2 | 28 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | I_ROALD_DAHL_TITLES | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / ROALD_DAHL_NOVELS@SEL$1
2 - SEL$1 / ROALD_DAHL_NOVELS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TITLE"='Esio Trot')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ROALD_DAHL_NOVELS".ROWID[ROWID,10], "TITLE"[VARCHAR2,100],
"PUBLISHED_YEAR"[NUMBER,22]
2 - "ROALD_DAHL_NOVELS".ROWID[ROWID,10]
As part of operation 2, it performs an index unique scan, which tells me that 'Esio Trot' is expected to appear only once in the cluster. The execution plan also says that for that operation, it expects to return only one row.
The column projection information tells me that it expects to return a single column (which will be a ROWID for the table ROALD_DAHL_NOVELS), so this tells me that the total number of ROWIDs returned from that operation will be 1 (1 row at 1 ROWID per row). Since each of the two rows in the table ROALD_DAHL_NOVELS has a different ROWID, then this operation can only be used to return a single row from the table.
When the TABLE ACCESS CLUSTER operation is performed, the execution plan then (correctly) expects two rows to be returned, which is what I find confusing. If these rows are being accessed by ROWID, then I would expect the previous operation to return (at least) two ROWIDs. If they are not being accessed by ROWID, I would not expect the previous operation to return and ROWIDs.
Also, in the TABLE ACCESS CLUSTER, the ROWID of the table ROALD_DAHL_NOVELS is listed in the column projection information section. I am not attempting to select the ROWID, so I would not expect it to be returned from that operation. If anywhere, I would expect it to be in the predicate information section.
Additional investigation
I tried inserting the same row into the table repeatedly, until it contained 65536 identical copies of the same row. After gathering stats and querying USER_INDEXES for the index I_ROALD_DAHL_TITLES, we got the following:
UNIQUENESS DISTINCT_KEYS AVG_DATA_BLOCKS_PER_KEY
UNIQUE 1 109
As I understand it, this tells us:
- The index is unique, so we expect each key to appear once in the index
- The index has only one distinct key ('Esio Trot'), so must have exactly one entry
- The index expects our one key to match to several rows in the table, across 109 blocks
This seems paradoxical - for one key to match to several rows in the table would mean that there must be several entries in the index for that key (each matching to a different ROWID), which would contradict the index being unique.
When checking USER_EXTENTS, the index only uses a single extent of 65536 bytes, which is not enough space to hold information for each of the ROWIDs in the table.