UNIQUE LOCAL (Partitioned) Index

It is easy to create a default GLOBAL Index that is defined as a Unique Index on a column (or composite of columns) containing unique values.

But what if you have a Partitioned Table and want to create a LOCAL (i.e. equi-partitioned with the table) Index as a Unique Index ?  Are there any constraints ?

Let me demonstrate a Partitioned table listing Users by Region Code.

SQL> create table users
  2  (region_code varchar2(3),
  3   username varchar2(30),
  4   account_status varchar2(32),
  5   created date,
  6   profile varchar2(128))
  7  partition by range (region_code)
  8  (partition a_m values less than ('N'),
  9   partition n_r values less than ('S'),
 10   partition s_z values less than (MAXVALUE))
 11  /

Table created.

SQL> 
SQL> insert into users
  2  select substr(username,1,3), username, account_status, created, profile
  3  from dba_users
  4  /

39 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','USERS');

PL/SQL procedure successfully completed.

SQL>
SQL> col partition_name format a30
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'USERS'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> 

I now verify that I can create a GLOBAL (non-partitioned) Unique Index on USERNAME.

SQL> create unique index users_username_u1 on users(username) global;

Index created.

SQL> drop index users_username_u1;

Index dropped.

SQL> create unique index users_username_u1 on users(username);

Index created.

SQL> drop index users_username_u1;

Index dropped.

SQL> 

I now verify that I can create a Non-Unique LOCAL Index (being equi-partitioned, the index is partitioned by REGION_CODE).  (Being equi-partitioned, the default behaviour is the Index Partition Names inherit from the Table Partition Names).

SQL> create index users_username_l1 on users(username) local;

Index created.

SQL> select partition_name, num_rows
  2  from user_ind_partitions
  3  where index_name = 'USERS_USERNAME_L1'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> 
SQL> drop index users_username_l1;

Index dropped.

SQL> 

I've proven (with the GLOBAL Index) that USERNAME is Unique across the whole table.  Can I create a Unique LOCAL Index on this column ?

SQL> create unique index users_username_u_l1 on users(username) local;
create unique index users_username_u_l1 on users(username) local
                                           *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index


SQL> 

Note the Error Message. The Partition Key must be a subset of the Unique Index columns.  Let me try adding the Partition Key  (in my example table, the Partition Key is a single column -- it could be a composite of multiple columns.  In that case all the columns of the Partition Key must for a subset of the Unique Index).

SQL> create unique index users_rc_un_u_l1 on users(region_code, username) local;

Index created.

SQL> select partition_name, num_rows
  2  from user_ind_partitions
  3  where index_name = 'USERS_RC_UN_U_L1'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> drop index users_rc_un_u_l1;

Index dropped.

SQL> create unique index users_un_rc_u_l1 on users(username, region_code) local;

Index created.

SQL> select partition_name, num_rows
  2  from user_ind_partitions
  3  where index_name = 'USERS_UN_RC_U_L1'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> 

It doesn't matter if I put the Partition Key (REGION_CODE) before or after the target column(s) (USERNAME) when I create the LOCAL Unique Index.  What is necessary is that the Partition Key be a subset of the Unique Index definition.
.
.
.