The table below is partitioned by range, using the column NOTE_ID as the partition key: CREATE TABLE SCOTT.ARCHIVED_NOTES ( NOTE_ID NUMBER(10) NOT NULL, PPT_ID NUMBER(10), REGISTRED_DATE DATE NOT NULL, EDITED_DATE DATE, ALTERED_BY VARCHAR2(30 BYTE) NOT NULL, ALTERED_DATE DATE NOT NULL, CONTENT_CODE VARCHAR2(4 BYTE), NOTEFORMAT VARCHAR2(1 BYTE) ) TABLESPACE USERS PARTITION BY RANGE (NOTE_ID) ( PARTITION ARCHIVED20 VALUES LESS THAN (21000000), PARTITION ARCHIVED21 VALUES LESS THAN (22000000), PARTITION ARCHIVED22 VALUES LESS THAN (23000000) ) ;
Create a local index:
CREATE UNIQUE INDEX SCOTT.ARCHIVED_NOTES_PK ON SCOTT.ARCHIVED_NOTES (NOTE_ID) LOCAL ( PARTITION ARCHIVED20, PARTITION ARCHIVED21, PARTITION ARCHIVED22 );
Confirm its existence:
| INDEX_NAME | PARTITIONED |
|---|---|
| ARCHIVED_NOTES_PK | YES |
The index we just created can be used to support a unique or a primary key constraint:
ALTER TABLE ARCHIVED_NOTES ADD CONSTRAINT ARCHIVED_NOTES_PK PRIMARY KEY (NOTE_ID) USING INDEX ARCHIVED_NOTES_PK;
Confirm that the expected index partitions have been created:
SELECT INDEX_NAME,PARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME = (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES');
| INDEX_NAME | PARTITION_NAME |
|---|---|
| ARCHIVED_NOTES_PK | ARCHIVED20 |
| ARCHIVED_NOTES_PK | ARCHIVED21 |
| ARCHIVED_NOTES_PK | ARCHIVED22 |
Add a new partition:
ALTER TABLE SCOTT.ARCHIVED_NOTES ADD PARTITION ARCHIVED23 VALUES LESS THAN (23000000);
If you confirm the index partitions again, you'll see that a new index partition has been added:
| INDEX_NAME | PARTITION_NAME |
|---|---|
| ARCHIVED_NOTES_PK | ARCHIVED23 |
Let's take a deeper look at the properties of this index:
SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE, I.SUBPARTITIONING_TYPE, I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION FROM USER_PART_INDEXES I JOIN USER_IND_COLUMNS C ON (I.INDEX_NAME = C.INDEX_NAME) WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE PARTITIONED='YES') ORDER BY 1,2,7;
| TABLE_NAME | INDEX_NAME | PARTITIONING_TYPE | SUBPARTITIONING_TYPE | LOCALITY | ALIGNMENT | COLUMN_NAME | COLUMN_POSITION |
|---|---|---|---|---|---|---|---|
| ARCHIVED_NOTES | ARCHIVED_NOTES_PK | RANGE | NONE | LOCAL | PREFIXED | NOTE_ID | 1 |
The "alignment" column of the above output reveals that the index is of type "prefixed", which means that the partitioning key is the first column in the index.
