oracle根据主键分区,oracle主键分区索引（转mos)-CSDN博客

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.0.6.0 and later

Information in this document applies to any platform.

PURPOSE

This article gives examples of how to create primary key partitioned indexes.

SCOPE

For users trying to create primary key partitioned indexes.

Note: This Document is not applicable 10g onwards ( See 10g onwards example )

DETAILS

How To Create Primary Key Partitioned Indexes:

==============================================

Below 10g:

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

10g Onwards Example:

=================

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

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>

SQL> ALTER TABLE test_a ADD CONSTRAINT pk_test_a PRIMARY KEY (col2, col1);

Table altered.

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> CREATE INDEX ix_test_b2 ON test_b(col1, col2) LOCAL;

CREATE INDEX ix_test_b2 ON test_b(col1, col2) LOCAL

ERROR at line 1:

ORA-01408: such column list already indexed

SQL> ALTER TABLE test_b ADD CONSTRAINT pk_test_b PRIMARY KEY (col1, col2);

Table altered.

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> 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> ALTER TABLE test_a DROP CONSTRAINT pk_test_a;

Table altered.

SQL> ALTER TABLE test_b DROP CONSTRAINT pk_test_b;

Table altered.

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.