Thursday, February 16, 2017

Create a local non-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 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