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)
);
