分区表主键如何使用分区索引

原文连接：http://blog.sina.com.cn/s/blog_4be7cab90100awz2.html

分区表主键如何使用分区索引？
主键索引失效，rebuild太慢了，
打算建立分区索引时才意识到以前没做过这样的工作，
建表时，在添加primary key时会自动生成索引，这个索引并不会进行随着表分区而分区。
于是查资料，找到这篇文章
文章中心就一句话：现在需要建立主键的列上创建一个分区索引，然后添加主键，oracle会自动选用这个索引作为主键的索引。


PURPOSE 
   This article gives examples of how to create primary key partitioned 
   indexes. 
  
SCOPE & APPLICATION 
   For users trying to create primary key partitioned indexes. 
  
  
How To Create Primary Key Partitioned Indexes: 
============================================== 
  
Example: 
  
SQL> -- Create partitioned table TEST_A 
SQL> 
SQL>  CREATE TABLE test_a (col1 number, col2 number, col3 varchar2(20)) 
   2   PARTITION BY RANGE (col1, col2) 
   3   (partition part_test_a_1 values less than (10, 100), 
   4    partition part_test_a_2 values less than (20, 200), 
   5    partition part_test_a_3 values less than (30, 300), 
   6    partition part_test_a_4 values less than (40, 400)); 

Table created. 
  
SQL> -- Create partitioned table TEST_B 
SQL> 
SQL>  CREATE TABLE test_b (col1 number, col2 number, col3 varchar2(20)) 
   2   PARTITION BY RANGE (col1, col2) 
   3   (partition part_test_b_1 values less than (10, 100), 
   4    partition part_test_b_2 values less than (20, 200), 
   5    partition part_test_b_3 values less than (30, 300), 
   6    partition part_test_b_4 values less than (40, 400)); 
  
Table created. 
  
SQL> -- Create a non-unique local partitioned index, IX_TEST_A, 
SQL> -- on TEST_A 
SQL> 
SQL>  CREATE INDEX ix_test_a ON test_a(col1, col2) 
   2   LOCAL 
   3   (partition ix_test_a_1, 
   4    partition ix_test_a_2, 
   5    partition ix_test_a_3, 
   6    partition ix_test_a_4); 
  
Index created. 
  
  
SQL> -- Create a unique global partitioned index, IX_TEST_B, 
SQL> -- on TEST_B 
SQL> 
SQL>  CREATE UNIQUE INDEX ix_test_b1 ON test_b(col1, col2) 
   2   GLOBAL PARTITION BY RANGE (col1, col2) 
   3   (partition ix_test_b1_1 values less than (20, 200), 
   4    partition ix_test_b1_2 values less than (maxvalue, maxvalue)); 
  
Index created. 
  
  
SQL> -- Add a primary key constraint, PK_TEST_A, to TEST_A 
SQL> 
SQL> ALTER TABLE test_a ADD CONSTRAINT pk_test_a 
2    PRIMARY KEY (col2, col1); 
  
Table altered. 
  
  
SQL> -- Attempt to drop index IX_TEST_A; note the following error... 
SQL> 
SQL> DROP INDEX ix_test_a; 
drop index ix_test_a 
* 
ERROR at line 1: 
ORA-02429: cannot drop index used for enforcement of unique/primary key 
  
  
SQL> -- Attempt to create a second index, IX_TEST_B2 on TEST_B 
SQL> -- using the same columns used to partition IX_TEST_B1. 
SQL> -- Note the following error... 
SQL> 
SQL>  CREATE INDEX ix_test_b2 ON test_b(col1, col2) 
   2   LOCAL; 
create index ix_test_b2 on test_b(col1, col2) 
                 * 
ERROR at line 1: 
ORA-01408: such column list already indexed 
  
  
SQL> -- Add a primary key constraint, PK_TEST_B, to TEST_B 
SQL> 
SQL>  ALTER TABLE test_b ADD CONSTRAINT pk_test_b 
   2   PRIMARY KEY (col1, col2); 
  
Table altered. 
  
  
SQL> -- Attempt to drop index IX_TEST_B1; note the following error... 
SQL> 
SQL> DROP INDEX ix_test_b1; 
drop index ix_test_b1 
* 
ERROR at line 1: 
ORA-02429: cannot drop index used for enforcement of unique/primary key 
  
  
SQL> -- A listing of the indexes and their associated partitions. 
SQL> 
SQL>  SELECT index_name, partition_name, status 
   2   FROM user_ind_partitions 
   3   ORDER BY index_name, partition_name; 
  
INDEX_NAME     PARTITION_NAME  STATUS 
-----------    --------------- -------- 
IX_TEST_A      IX_TEST_A_1     USABLE 
IX_TEST_A      IX_TEST_A_2     USABLE 
IX_TEST_A      IX_TEST_A_3     USABLE 
IX_TEST_A      IX_TEST_A_4     USABLE 
  
IX_TEST_B1     IX_TEST_B1_1    USABLE 
IX_TEST_B1     IX_TEST_B1_2    USABLE 
  
6 rows selected. 
  
  
SQL> -- Drop the primary key constraint from TEST_A 
SQL> 
SQL> ALTER TABLE test_a DROP CONSTRAINT pk_test_a; 
  
Table altered. 
  
  
SQL> -- Drop the primary key constraint from TEST_B 
SQL> 
SQL> ALTER TABLE test_b DROP CONSTRAINT pk_test_b; 
  
Table altered. 
  
  
SQL> -- A listing of the indexes and their associated partitions. 
SQL> -- Note that while IX_TEST_A, the non-unique local partitioned 
SQL> -- index, remains and has a status of USABLE. 
SQL> -- IX_TEST_B, the unique global partitioned index, has been 
SQL> -- dropped. 
SQL> 
SQL>  SELECT index_name, partition_name, status 
   2   FROM user_ind_partitions 
   3   ORDER BY index_name, partition_name; 
  
INDEX_NAME       PARTITION_NAME                 STATUS 
---------------  --------------                 -------- 
  
IX_TEST_A        IX_TEST_A_1                   USABLE 
IX_TEST_A        IX_TEST_A_2                   USABLE 
IX_TEST_A        IX_TEST_A_3                   USABLE 
IX_TEST_A        IX_TEST_A_4                   USABLE 
  
  
The primary key uses the underlying index if the index is built using  
the same columns as defined in the primary key.  This is consistent  
without regard to whether the index was created as a unique or non-unique 
index, or if it is a global or local partioned index.  It is important  
to note that while in the example a primary key was established on a  
non-unique index, this will only occur if the values within the index  
are in fact unique.  Attempting to enable a primary key constraint when  
duplicate values are present within the index will result in the  
following error: 
  
"ORA-02437: cannot enable (STEELY.PK_TEST_B) - primary key violated." 
  
Two indexes cannot be created using the same ordered columns.  This was 
demonstrated above when attempting to create a second index on table  
TEST_B.  This resulted in the following error:  
  
"ORA-01408: such column list already indexed."  
  
However, changing the order of the columns will permit the creation of  
additional indexes using the same columns. 
  
Contrary to the previous note, the column order for index IX_TEST_A and  
the definition for the primary key PK_TEST_A were reversed. Yet the  
primary key still used IX_TEST_A as the underlying index. 
  
When dropping a primary key constraint from a table, the corresponding  
index is also dropped if the index was created as a UNIQUE index. This  
behavior is consistent for both LOCAL as well as GLOBAL partitioned  
indexes. 
  
To receive the full benefits of partitioning, users/DBA must use the  
STORAGE clause when creating partitioned tables/indices.

---------------------------------------------------------------
如果表已经分完区了，并且数据量比较大的话，不方便重建表的话，可以直接删除主键，
然后利用上边的语句，进行主键索引的分区，分区列字段要包含分区键。

1) alter table 表 drop constraint 主键 cascade;
2) ALTER TABLE 表 ADD CONSTRAINT 主键名称 PRIMARY KEY (主键,分区键) USING INDEX LOCAL