Adding covering fields to a Primary Key Index

This is something to file under the (admittedly rather large) category of things that I wasn’t aware that the Oracle database could do.

While tuning a query, I wanted to use a common technique of adding fields to an index to eliminate a “Table Access by Index RowID” operation, however this particular case was a complicated  by the fact that the index was supporting the primary key, and the table was large and frequently accessed.

This is probably easiest demonstrated by the (much simplified) example below:

SQL> create table singles(id number generated always as identity,
2                       artist varchar2(255),
3                       title varchar2(255),
4                       constraint singles_pk primary key (id));

Table SINGLES created.

SQL>
SQL> insert into singles (artist,
2                        title)
3              values ('Chesney Hawkes',
4                      'The One And Only');

1 row inserted.

SQL> commit;

Commit complete.

SQL> select index_name from user_indexes where table_name = 'SINGLES';
INDEX_NAME
_____________
SINGLES_PK

SQL> select artist from singles where id = 1;
ARTIST
_________________
Chesney Hawkes

SQL> select * from dbms_xplan.display_cursor(format=>'BASIC');
PLAN_TABLE_OUTPUT
_____________________________________________________
EXPLAINED SQL STATEMENT:
------------------------
select artist from singles where id = 1

Plan hash value: 3923658952

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| SINGLES    |
|   2 |   INDEX UNIQUE SCAN         | SINGLES_PK |
--------------------------------------------------

14 rows selected.

Note that adding a new index on (id, artist) makes the plan more efficient:

SQL> create index i_singles_covering on singles(id, artist);

Index I_SINGLES_COVERING created.

SQL> select artist from singles where id = 1;
ARTIST
_________________
Chesney Hawkes

SQL> select * from dbms_xplan.display_cursor(format=>'BASIC');
PLAN_TABLE_OUTPUT
__________________________________________________
EXPLAINED SQL STATEMENT:
------------------------
select artist from singles where id = 1

Plan hash value: 1012019734

-----------------------------------------------
| Id  | Operation        | Name               |
-----------------------------------------------
|   0 | SELECT STATEMENT |                    |
|   1 |  INDEX RANGE SCAN| I_SINGLES_COVERING |
-----------------------------------------------

13 rows selected.

However we’ve now got two indexes, SINGLES_PK on (id) and I_SINGLES_COVERING on (id, artist).  SINGLES_PK is redundant, but being used to support the Primary Key:

SQL> select index_name from user_indexes where table_name = 'SINGLES';
           INDEX_NAME
_____________________
SINGLES_PK
I_SINGLES_COVERING

Now it is possible for a primary key to be supported by I_SINGLES_COVERING, but initially I thought I’d have to choose between dropping and re-creating the primary key to use the new index, or leaving the system in the non-optimal state of having the two indexes.

However I came across this blog post from Richard Foote, which referenced another post from Jonathan Lewis.  It describes the following technique of modifying the constraint to use the new index without needing to re-recreate it.  It’s worth noting that the index SINGLES_PK that the database automatically created to initially support the primary key gets dropped during this operation.

SQL> alter table singles
  2        modify constraint singles_pk
  3        using index i_singles_covering;
Table SINGLES altered.


SQL> select index_name from user_indexes where table_name = 'SINGLES';
INDEX_NAME
_____________________
I_SINGLES_COVERING

One thing I observed my testing was that if I created i_singles_covering as a unique index (id is unique as it’s the primary key, so obviously combination of id & artist must also be unique) then the database was unwilling to use this index to support the primary key:

SQL> create unique index i_singles_covering on singles(id, artist);

Index I_SINGLES_COVERING created.

SQL> alter table singles
2          modify constraint singles_pk
3          using index i_singles_covering;

ORA-14196: Specified index cannot be used to enforce the constraint.
14196. 00000 - "Specified index cannot be used to enforce the constraint."
*Cause: The index specified to enforce the constraint is unsuitable
for the purpose.
*Action: Specify a suitable index or allow one to be built automatically.

This case is documented by Oracle Support Document ID 577253.1 which states:

We cannot use a prefix of a unique index to enforce a unique constraint. We can use a whole unique index or a prefix of a non-unique index to do that. This is the way Oracle was designed.

However I can’t off-hand think of any technical reason for this limitation.