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.