Thursday, February 16, 2017

Create a globally partitioned hash index


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