It is entirely possible with later releases of Oracle to use a non-unique index to enforce a primary key constraint. An advantage of this strategy is the index remains if the constraint is, at some time dropped, provided the index was not created as part of the ‘add constraint’ statement. Disadvantages to this strategy can include changed redo/undo generation volume and execution plans that may not be the same as when a unique index is used. It is possible to ‘replace’ the non-unique index with a unique index of the same name, and do it without an outage. Yes, it’s a remote chance that such a task may be necessary, but knowing how to do it in Oracle 11.2.0.x and earlier releases can make things much easier if this ever does come to pass.
We start this endeavor by creating a table, a non-unique index and a primary key constraint that uses the non-unique index:
SQL>
SQL> --
SQL> -- Create and load table
SQL> --
SQL> create table uniq_pk_idx_tst(
2 uniq_key number,
3 somestuff varchar2(40),
4 somedt date,
5 someother varchar2(24));
Table created.
SQL>
SQL> begin
2 for i in 1..1000000 loop
3 insert into uniq_pk_idx_tst(uniq_key, somestuff, somedt, someother)
4 values(i, 'Some stuff '||i, sysdate+i, 'Other '||i);
5 end loop;
6
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Create non-unique index
SQL> --
SQL> create index uniq_pk_idx_tst_pk on uniq_pk_idx_tst(uniq_key);
Index created.
SQL> --
SQL> -- Use non-unique index to enforce primary key
SQL> --
SQL> alter table uniq_pk_idx_tst add constraint uniq_pk_idx_tst_pk primary key(uniq_key);
Table altered.
SQL>
SQL> --
SQL> -- Generate stats then query the table
SQL> --
SQL> -- See if non-unique index increases query costs
SQL> -- and undo generation
SQL> --
SQL> exec dbms_stats.gather_table_stats('BING', 'UNIQ_PK_IDX_TST',estimate_percent=>100, method_opt=>'for all columns size auto', cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
Now we query the table and see the plan in use.
SQL> set autotrace on
SQL>
SQL> select uniq_key, somestuff, someother
2 from uniq_pk_idx_tst
3 where uniq_key between 6010 and 6100;
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6010 Some stuff 6010 Other 6010
6011 Some stuff 6011 Other 6011
6012 Some stuff 6012 Other 6012
6013 Some stuff 6013 Other 6013
6014 Some stuff 6014 Other 6014
6015 Some stuff 6015 Other 6015
6016 Some stuff 6016 Other 6016
6017 Some stuff 6017 Other 6017
6018 Some stuff 6018 Other 6018
6019 Some stuff 6019 Other 6019
6020 Some stuff 6020 Other 6020
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6021 Some stuff 6021 Other 6021
6022 Some stuff 6022 Other 6022
6023 Some stuff 6023 Other 6023
6024 Some stuff 6024 Other 6024
6025 Some stuff 6025 Other 6025
6026 Some stuff 6026 Other 6026
6027 Some stuff 6027 Other 6027
6028 Some stuff 6028 Other 6028
6029 Some stuff 6029 Other 6029
6030 Some stuff 6030 Other 6030
6031 Some stuff 6031 Other 6031
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6032 Some stuff 6032 Other 6032
6033 Some stuff 6033 Other 6033
6034 Some stuff 6034 Other 6034
6035 Some stuff 6035 Other 6035
6036 Some stuff 6036 Other 6036
6037 Some stuff 6037 Other 6037
6038 Some stuff 6038 Other 6038
6039 Some stuff 6039 Other 6039
6040 Some stuff 6040 Other 6040
6041 Some stuff 6041 Other 6041
6042 Some stuff 6042 Other 6042
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6043 Some stuff 6043 Other 6043
6044 Some stuff 6044 Other 6044
6045 Some stuff 6045 Other 6045
6046 Some stuff 6046 Other 6046
6047 Some stuff 6047 Other 6047
6048 Some stuff 6048 Other 6048
6049 Some stuff 6049 Other 6049
6050 Some stuff 6050 Other 6050
6051 Some stuff 6051 Other 6051
6052 Some stuff 6052 Other 6052
6053 Some stuff 6053 Other 6053
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6054 Some stuff 6054 Other 6054
6055 Some stuff 6055 Other 6055
6056 Some stuff 6056 Other 6056
6057 Some stuff 6057 Other 6057
6058 Some stuff 6058 Other 6058
6059 Some stuff 6059 Other 6059
6060 Some stuff 6060 Other 6060
6061 Some stuff 6061 Other 6061
6062 Some stuff 6062 Other 6062
6063 Some stuff 6063 Other 6063
6064 Some stuff 6064 Other 6064
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6065 Some stuff 6065 Other 6065
6066 Some stuff 6066 Other 6066
6067 Some stuff 6067 Other 6067
6068 Some stuff 6068 Other 6068
6069 Some stuff 6069 Other 6069
6070 Some stuff 6070 Other 6070
6071 Some stuff 6071 Other 6071
6072 Some stuff 6072 Other 6072
6073 Some stuff 6073 Other 6073
6074 Some stuff 6074 Other 6074
6075 Some stuff 6075 Other 6075
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6076 Some stuff 6076 Other 6076
6077 Some stuff 6077 Other 6077
6078 Some stuff 6078 Other 6078
6079 Some stuff 6079 Other 6079
6080 Some stuff 6080 Other 6080
6081 Some stuff 6081 Other 6081
6082 Some stuff 6082 Other 6082
6083 Some stuff 6083 Other 6083
6084 Some stuff 6084 Other 6084
6085 Some stuff 6085 Other 6085
6086 Some stuff 6086 Other 6086
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6087 Some stuff 6087 Other 6087
6088 Some stuff 6088 Other 6088
6089 Some stuff 6089 Other 6089
6090 Some stuff 6090 Other 6090
6091 Some stuff 6091 Other 6091
6092 Some stuff 6092 Other 6092
6093 Some stuff 6093 Other 6093
6094 Some stuff 6094 Other 6094
6095 Some stuff 6095 Other 6095
6096 Some stuff 6096 Other 6096
6097 Some stuff 6097 Other 6097
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6098 Some stuff 6098 Other 6098
6099 Some stuff 6099 Other 6099
6100 Some stuff 6100 Other 6100
91 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2575518852
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92 | 3312 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| UNIQ_PK_IDX_TST | 92 | 3312 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | UNIQ_PK_IDX_TST_PK | 92 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)
Statistics
----------------------------------------------------------
8 recursive calls
4 db block gets
18 consistent gets
0 physical reads
540 redo size
4747 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
91 rows processed
SQL>
SQL> --
SQL> -- Update the table
SQL> --
SQL> -- Report undo generated
SQL> --
SQL> update uniq_pk_idx_tst
2 set someother = 'YOYO'
3 where uniq_key between 6010 and 6100;
91 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1984512805
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 92 | 1656 | 4 (0)| 00:00:01 |
| 1 | UPDATE | UNIQ_PK_IDX_TST | | | | |
|* 2 | INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK | 92 | 1656 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)
Statistics
----------------------------------------------------------
3 recursive calls
93 db block gets
7 consistent gets
0 physical reads
23440 redo size
846 bytes sent via SQL*Net to client
835 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
91 rows processed
SQL>
SQL> set autotrace off
SQL> commit;
Commit complete.
SQL>
SQL> set autotrace on
SQL> update uniq_pk_idx_tst
2 set someother = null
3 where uniq_key between 6010 and 6100;
91 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1984512805
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 92 | 1656 | 4 (0)| 00:00:01 |
| 1 | UPDATE | UNIQ_PK_IDX_TST | | | | |
|* 2 | INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK | 92 | 1656 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)
Statistics
----------------------------------------------------------
1 recursive calls
95 db block gets
3 consistent gets
0 physical reads
0 redo size
846 bytes sent via SQL*Net to client
833 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
91 rows processed
SQL>
SQL> set autotrace off
SQL> commit;
Commit complete.
SQL>
SQL>
The bytes of redo generated by the update for the non-unique index is 23440. This give us a reference for the next section, where the non-unique index is eventually replaced by a unique index of the same name. Note we need a ‘replacement’ index so that queries run in the interim still can use an index access path:
SQL>
SQL> --
SQL> -- Create a non-unique 'replacement' index
SQL> --
SQL> -- Modify the PK constraint to use this new index
SQL> --
SQL> create index uniq_pk_idx_tst_nu on uniq_pk_idx_tst(uniq_key, 0) online;
Index created.
SQL>
SQL> alter table uniq_pk_idx_tst modify primary key using index uniq_pk_idx_tst_nu;
Table altered.
SQL>
SQL> --
SQL> -- Drop the old PK index
SQL> --
SQL> drop index uniq_pk_idx_tst_pk;
Index dropped.
SQL>
SQL> --
SQL> -- Create a new, unique PK index with the original name
SQL> --
SQL> -- Re-generate statistics
SQL> --
SQL> create unique index uniq_pk_idx_tst_pk on uniq_pk_idx_tst(uniq_key) online;
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats('BING', 'UNIQ_PK_IDX_TST',estimate_percent=>100, method_opt=>'for all columns size auto', cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Alter the primary key to use this new unique index
SQL> --
SQL> alter table uniq_pk_idx_tst modify primary key using index uniq_pk_idx_tst_pk;
Table altered.
SQL>
SQL> --
SQL> -- Drop the interim index
SQL> --
SQL> drop index uniq_pk_idx_tst_nu;
Index dropped.
SQL>
Now the constraint is using a unique index. Let’s again check the plan and see how much redo is generated for updates:
SQL>
SQL> --
SQL> -- Query table again
SQL> --
SQL> -- See if unique index improves cost and
SQL> -- reduces undo
SQL> --
SQL> set autotrace on
SQL>
SQL> select uniq_key, somestuff, someother
2 from uniq_pk_idx_tst
3 where uniq_key between 6010 and 6100;
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6010 Some stuff 6010
6011 Some stuff 6011
6012 Some stuff 6012
6013 Some stuff 6013
6014 Some stuff 6014
6015 Some stuff 6015
6016 Some stuff 6016
6017 Some stuff 6017
6018 Some stuff 6018
6019 Some stuff 6019
6020 Some stuff 6020
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6021 Some stuff 6021
6022 Some stuff 6022
6023 Some stuff 6023
6024 Some stuff 6024
6025 Some stuff 6025
6026 Some stuff 6026
6027 Some stuff 6027
6028 Some stuff 6028
6029 Some stuff 6029
6030 Some stuff 6030
6031 Some stuff 6031
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6032 Some stuff 6032
6033 Some stuff 6033
6034 Some stuff 6034
6035 Some stuff 6035
6036 Some stuff 6036
6037 Some stuff 6037
6038 Some stuff 6038
6039 Some stuff 6039
6040 Some stuff 6040
6041 Some stuff 6041
6042 Some stuff 6042
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6043 Some stuff 6043
6044 Some stuff 6044
6045 Some stuff 6045
6046 Some stuff 6046
6047 Some stuff 6047
6048 Some stuff 6048
6049 Some stuff 6049
6050 Some stuff 6050
6051 Some stuff 6051
6052 Some stuff 6052
6053 Some stuff 6053
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6054 Some stuff 6054
6055 Some stuff 6055
6056 Some stuff 6056
6057 Some stuff 6057
6058 Some stuff 6058
6059 Some stuff 6059
6060 Some stuff 6060
6061 Some stuff 6061
6062 Some stuff 6062
6063 Some stuff 6063
6064 Some stuff 6064
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6065 Some stuff 6065
6066 Some stuff 6066
6067 Some stuff 6067
6068 Some stuff 6068
6069 Some stuff 6069
6070 Some stuff 6070
6071 Some stuff 6071
6072 Some stuff 6072
6073 Some stuff 6073
6074 Some stuff 6074
6075 Some stuff 6075
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6076 Some stuff 6076
6077 Some stuff 6077
6078 Some stuff 6078
6079 Some stuff 6079
6080 Some stuff 6080
6081 Some stuff 6081
6082 Some stuff 6082
6083 Some stuff 6083
6084 Some stuff 6084
6085 Some stuff 6085
6086 Some stuff 6086
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6087 Some stuff 6087
6088 Some stuff 6088
6089 Some stuff 6089
6090 Some stuff 6090
6091 Some stuff 6091
6092 Some stuff 6092
6093 Some stuff 6093
6094 Some stuff 6094
6095 Some stuff 6095
6096 Some stuff 6096
6097 Some stuff 6097
UNIQ_KEY SOMESTUFF SOMEOTHER
---------- ---------------------------------------- ------------------------
6098 Some stuff 6098
6099 Some stuff 6099
6100 Some stuff 6100
91 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2575518852
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92 | 3312 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| UNIQ_PK_IDX_TST | 92 | 3312 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | UNIQ_PK_IDX_TST_PK | 92 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)
Statistics
----------------------------------------------------------
59 recursive calls
46 db block gets
74 consistent gets
0 physical reads
4972 redo size
4033 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
91 rows processed
SQL>
SQL> --
SQL> -- Update the table
SQL> --
SQL> -- Report undo generated
SQL> --
SQL> update uniq_pk_idx_tst
2 set someother = 'YOYO'
3 where uniq_key between 6010 and 6100;
91 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1984512805
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 92 | 1656 | 4 (0)| 00:00:01 |
| 1 | UPDATE | UNIQ_PK_IDX_TST | | | | |
|* 2 | INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK | 92 | 1656 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)
Statistics
----------------------------------------------------------
3 recursive calls
93 db block gets
7 consistent gets
0 physical reads
21280 redo size
847 bytes sent via SQL*Net to client
835 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
91 rows processed
SQL>
SQL> set autotrace off
SQL> commit;
Commit complete.
SQL>
SQL> set autotrace on
SQL> update uniq_pk_idx_tst
2 set someother = null
3 where uniq_key between 6010 and 6100;
91 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1984512805
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 92 | 1656 | 4 (0)| 00:00:01 |
| 1 | UPDATE | UNIQ_PK_IDX_TST | | | | |
|* 2 | INDEX RANGE SCAN| UNIQ_PK_IDX_TST_PK | 92 | 1656 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UNIQ_KEY">=6010 AND "UNIQ_KEY"<=6100)
Statistics
----------------------------------------------------------
1 recursive calls
95 db block gets
3 consistent gets
0 physical reads
0 redo size
848 bytes sent via SQL*Net to client
833 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
91 rows processed
SQL>
SQL> set autotrace off
SQL> commit;
Commit complete.
SQL>
SQL>
The bytes of redo generated with the unique index is reported as 21280, slightly less than with the non-unique index (23440); the plan didn’t change (and I expect that may be a rare occurrence, depending on the data). Since we only updated a small portion of the data the difference in redo generation could be much greater for larger updates.
The more you know the easier being a DBA can be.
