Wednesday, February 15, 2017

Create a local prefixed partitioned index


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.



No comments:

Post a Comment