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 global partitioned index:
CREATE INDEX SCOTT.REGISTRED_DATE_SK1 ON SCOTT.ARCHIVED_NOTES (REGISTRED_DATE) LOCAL ( PARTITION ARCHIVED20, PARTITION ARCHIVED21, PARTITION ARCHIVED22 )
Confirm its existence:
SELECT INDEX_NAME,PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME='ARCHIVED_NOTES';
INDEX_NAME | PARTITIONED |
---|---|
REGISTRED_DATE_SK1 | YES |
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 |
---|---|
REGISTRED_DATE_SK1 | ARCHIVED20 |
REGISTRED_DATE_SK1 | ARCHIVED21 |
REGISTRED_DATE_SK1 | ARCHIVED22 |
Add a new partition:
ALTER TABLE SCOTT.ARCHIVED_NOTES ADD PARTITION ARCHIVED23 VALUES LESS THAN (24000000);
If you confirm the index partitions again, you'll see that a new index partition has been added:
INDEX_NAME | PARTITION_NAME |
---|---|
REGISTRED_DATE_SK1 | 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 | REGISTRED_DATE_SK1 | RANGE | NONE | LOCAL | NON_PREFIXED | REGISTRED_DATE | 1 |
The "alignment" column of the above output reveals that the index is of type "non_prefixed", which means that the partitioning key is not the first column in the index.
No comments:
Post a Comment