Thursday, February 16, 2017

Create a globally partitioned range index


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 range partitioned index. Notice the GLOBAL keyword:
CREATE INDEX SCOTT.ARCHIVED_NOTES_IDX1 
ON SCOTT.ARCHIVED_NOTES(NOTE_ID)
GLOBAL PARTITION BY RANGE(NOTE_ID)(  
  PARTITION ARCHIVED20 VALUES LESS THAN (21000000),  
  PARTITION ARCHIVED21 VALUES LESS THAN (22000000),  
  PARTITION ARCHIVED22 VALUES LESS THAN (23000000),
  PARTITION ARCHIVED_OTHERS VALUES LESS THAN (MAXVALUE)
);

Also note that Oracle required the MAXVALUES clause as the last partition in the index, to ensure that all rows in the table will be represented in the index. Without MAXVALUES, Oracle will throw the error:
ORA-14021: MAXVALUE must be specified for all columns

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_IDX1 ARCHIVED20
ARCHIVED_NOTES_IDX1 ARCHIVED21
ARCHIVED_NOTES_IDX1 ARCHIVED22
ARCHIVED_NOTES_IDX1 ARCHIVED_OTHERS

You could also make the index UNIQUE:
CREATE UNIQUE INDEX SCOTT.ARCHIVED_NOTES_IDX1 ... 

A unique index like this could support a primary key constraint on the table, if desirable:
ALTER TABLE ARCHIVED_NOTES ADD CONSTRAINT ARCHIVED_NOTES_PK
PRIMARY KEY (NOTE_ID)
USING INDEX ARCHIVED_NOTES_IDX1;

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_IDX1 RANGE NONE GLOBAL PREFIXED NOTE_ID
1


For globally partitioned indexes, the "alignment" column will always show the index as "prefixed". This is the only supported index type for global partitioned indexes.

An important point regarding globally partitioned indexes is pointed out in the Oracle Documentation:

"Normally, a global index is not equipartitioned with the underlying table."

and

"There is nothing to prevent an index from being equipartitioned with the underlying table, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations. So an index that is equipartitioned with the underlying table should be created as LOCAL."

In other words, while the example above would work, it may not be a good idea. It would make better sense for the global partitioned index to be created on another column:
CREATE INDEX SCOTT.ARCHIVED_NOTES_IDX1 
ON SCOTT.ARCHIVED_NOTES(REGISTRED_DATE)
GLOBAL PARTITION BY RANGE(REGISTRED_DATE)(  
  PARTITION NOTES_2012 VALUES LESS THAN (to_date('01.01.2013', 'dd.mm.yyyy')),  
  PARTITION NOTES_2013 VALUES LESS THAN (to_date('01.01.2014', 'dd.mm.yyyy')),  
  PARTITION NOTES_2014 VALUES LESS THAN (to_date('01.01.2015', 'dd.mm.yyyy'))
  ,PARTITION ARCHIVED_OTHERS VALUES LESS THAN (MAXVALUE)
);


1 comment:

  1. my table is partitioned with range interval, how to implement the global indexing with range and interval same as the base table

    ReplyDelete