[20171211]UNIQUE LOCAL(Partitioned)Index

[20171211]UNIQUE LOCAL (Partitioned) Index.txt

--//如何在分割槽表中建立local unique index呢?自己對分割槽表這部分內容瞭解很少,參考連結:
--//https://hemantoracledba.blogspot.com/2017/11/unique-local-partitioned-index.html
--//重複測試,一些內容直接轉抄,不自己寫了.

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立測試環境:

create table users
(region_code varchar2(3),
username varchar2(30),
account_status varchar2(32),
created date,
profile varchar2(128))
partition by range (region_code)
(partition a_m values less than ('N'),
partition n_r values less than ('S'),
partition s_z values less than (MAXVALUE));

insert into users
select substr(username,1,3), username, account_status, created, profile
from dba_users;

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

col partition_name format a30
SCOTT@book> select partition_name, num_rows from user_tab_partitions where table_name = 'USERS' order by partition_position;
PARTITION_NAME NUM_ROWS
-------------- --------
A_M                  15
N_R                  10
S_Z                  13

3.測試1:
--//測試GLOBAL (non-partitioned) Unique Index on USERNAME.

SCOTT@book> create unique index users_username_u1 on users(username) global;
Index created.

SCOTT@book> drop index users_username_u1;
Index dropped.

SCOTT@book> create unique index users_username_u1 on users(username);
Index created.

SCOTT@book> drop index users_username_u1;
Index dropped.
--//OK!!實際上後面的global引數是對於的,預設就是global索引.

4.測試2:

--//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).

SCOTT@book> create index users_username_l1 on users(username) local;
Index created.

select partition_name, num_rows from user_ind_partitions where index_name = 'USERS_USERNAME_L1' order by partition_position;
SCOTT@book> select partition_name, num_rows from user_ind_partitions where index_name = 'USERS_USERNAME_L1' order by partition_position;
PARTITION_NAME NUM_ROWS
-------------- --------
A_M                  15
N_R                  10
S_Z                  13

SCOTT@book> drop index users_username_l1;
Index dropped.

5.測試3:
--//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 ?

SCOTT@book> 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

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).

SCOTT@book> create unique index users_rc_un_u_l1 on users(region_code, username) local;
Index created.

SCOTT@book> select partition_name, num_rows from user_ind_partitions where index_name = 'USERS_RC_UN_U_L1' order by partition_position;
PARTITION_NAME NUM_ROWS
-------------- --------
A_M                  15
N_R                  10
S_Z                  13

SCOTT@book> drop index users_rc_un_u_l1;
Index dropped.

SCOTT@book> create unique index users_un_rc_u_l1 on users(username, region_code) local;

Index created.
SCOTT@book> select partition_name, num_rows from user_ind_partitions where index_name = 'USERS_UN_RC_U_L1' order by partition_position;
PARTITION_NAME NUM_ROWS
-------------- --------
A_M                  15
N_R                  10
S_Z                  13

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.

--//實際上按照以上觀點,就是建立unique local index要包括Partition Key.