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