Partitioned table layout:
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 hash-partitioned index. Notice the GLOBAL keyword:
CREATE INDEX ARCHIVED_NOTES_IDX2 ON ARCHIVED_NOTES (REGISTRED_DATE) GLOBAL PARTITION BY HASH (REGISTRED_DATE) PARTITIONS 3;
Verify its existence:
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_IDX2 | SYS_P441 |
ARCHIVED_NOTES_IDX2 | SYS_P442 |
ARCHIVED_NOTES_IDX2 | SYS_P443 |
Let's take a deeper look at the index properties:
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_IDX2 | HASH | NONE | GLOBAL | PREFIXED | REGISTRED_DATE | 1 |
For global partitioned indexes, the "alignment" column will always show the index as "prefixed". This is the only supported index type for global partitioned indexes.
No comments:
Post a Comment