Locally Partitioned Unique Indexes on Reference Partitioned Tables

You can also read this article on the Go-Faster Oracle Blog

Normally, if you want to locally partition a unique index, you must include the partitioning key in the index key. Otherwise, you get will error ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index.?

CREATE TABLE PS_JRNL_HEADER 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL
…
) 
PARTITION BY RANGE (fiscal_year) INTERVAL (1)
(PARTITION FISCAL_YEAR_2016 VALUES LESS THAN (2017))
…
/

CREATE UNIQUE INDEX PS_JRNL_HEADER 
ON PS_JRNL_HEADER (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ)
LOCAL
/

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index        

This rule also applies to indexes on reference partitioned tables but in a slightly different way. The unique key on the child table cannot contain the partitioning key because those columns are only on the parent table. However, it can still be locally partitioned if it includes the parent table's primary key.?

Here is an example taken from PeopleSoft General Ledger. We can't add foreign keys to the PeopleSoft database, but we can add them to an archive database to support queries of archived data.

CREATE TABLE PS_JRNL_HEADER 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL
…
,CONSTRAINT PS_JRNL_HEADER PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ)
) 
PARTITION BY RANGE (fiscal_year) INTERVAL (1)
SUBPARTITION BY RANGE (accounting_period) 
SUBPARTITION TEMPLATE 
(SUBPARTITION accounting_period_00 VALUES LESS THAN (1)
…
,SUBPARTITION accounting_period_12 VALUES LESS THAN (13)
,SUBPARTITION accounting_period_max VALUES LESS THAN (maxvalue)
)
(PARTITION FISCAL_YEAR_2016 VALUES LESS THAN (2017))
COMPRESS FOR QUERY LOW 
/

CREATE TABLE PS_JRNL_LN 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL 
,JOURNAL_LINE NUMBER(9,0) NOT NULL
,LEDGER VARCHAR2(10 CHAR) NOT NULL
…
,CONSTRAINT PS_JRNL_LN PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ, JOURNAL_LINE, LEDGER)
,CONSTRAINT PS_JRNL_LN_FK FOREIGN KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ) REFERENCES PS_JRNL_HEADER 
)
PARTITION BY REFERENCE(PS_JRNL_LN_FK)
COMPRESS FOR ARCHIVE LOW 
/        

If I try to locally partition a unique index without one of the parent table's unique key columns, I get ORA-14039, which is exactly as I might expect.

CREATE UNIQUE INDEX PS_JRNL_LN2 
ON PS_JRNL_LN (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, /*UNPOST_SEQ,*/ JOURNAL_LINE, LEDGER) 
LOCAL COMPRESS 3
/

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
14039. 00000 -  "partitioning columns must form a subset of key columns of a UNIQUE index"
*Cause:    User attempted to create a UNIQUE partitioned index whose
           partitioning columns do not form a subset of its key columns
           which is illegal
*Action:   If the user, indeed, desired to create an index whose
           partitioning columns do not form a subset of its key columns,
           it must be created as non-UNIQUE; otherwise, correct the
           list of key and/or partitioning columns to ensure that the index'
           partitioning columns form a subset of its key columns        

What is going on here??

• JRNL_HEADER is partitioned on FISCAL_YEAR and sub-partitioned on ACCOUNTING_PERIOD.?
• JRNL_LN is reference-partitioned. Reference partitioning requires an enforced foreign key constraint. JRNL_LN has a foreign key constraint that references JRNL_HEADER. Thus, there is a 1:1 relationship of partitions on JRNL_LN (the child table), to partitions or in this case, sub-partitions on JRNL_HEADER (the parent table).

SELECT table_name, composite, partition_name, subpartition_count, partition_position, high_value
FROM   user_tab_partitions
WHERE  table_name LIKE 'PS_JRNL%' ORDER BY 1 DESC
/

                                                                 SubP Part
TABLE_NAME         COM PARTITION_NAME                           Count  Pos HIGH_VALUE
------------------ --- ---------------------------------------- ----- ---- --------------------
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_00        0    1
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_01        0    2
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_02        0    3
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_03        0    4
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_04        0    5
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_05        0    6
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_06        0    7
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_07        0    8
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_08        0    9
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_09        0   10
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_10        0   11
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_11        0   12
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_12        0   13
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_MAX       0   14
PS_JRNL_HEADER     YES FISCAL_YEAR_2016                            14    1 2017

15 rows selected.        

The partitioning keys recorded for the reference partitioned table JRNL_LN are the primary key columns on the parent table, although it is partitioned and sub-partitioned by different columns (FISCAL_YEAR and ACCOUNTING_PERIOD).

SELECT * FROM user_part_key_columns WHERE name LIKE 'PS_JRNL%' ORDER BY 1,2 desc, 4
/

NAME                 OBJEC COLUMN_NAME          COLUMN_POSITION COLLATED_COLUMN_ID
-------------------- ----- -------------------- --------------- ------------------
PS_JRNL_HEADER       TABLE FISCAL_YEAR                        1                   
PS_JRNL_LN           TABLE BUSINESS_UNIT                      1                   
PS_JRNL_LN           TABLE JOURNAL_ID                         2                   
PS_JRNL_LN           TABLE JOURNAL_DATE                       3                   
PS_JRNL_LN           TABLE UNPOST_SEQ                         4        

The parent table in a foreign key relationship must have a primary key, and the child table must reference it. The primary key of the parent table is a proxy for the partitioning key. The two tables effectively share the partitioning key through the 1:1 relationship of partitions. Each primary key on the parent table can only appear in a single sub-partition, and therefore, corresponding child rows can only appear in the corresponding partition in the child table. Therefore, the uniqueness of the locally partitioned index on the child table can be assured by inspecting just the local partition.?

Natural -v- Surrogate Keys

The example above uses natural keys. The key on the child table leads with the key columns of the parent table, followed by one or more additional key columns.? Thus it is possible to locally partition the primary or unique key index on the child table.??

However, if a data model uses surrogate keys then the key on the child table is a completely different column to the key on the parent table, and it would not be possible to locally partition an index unless it also contained the parent surrogate key, in which case it could not be used to enforce uniqueness.

• see also Ask Tom: Surrogate -v- Natural Keys

TL;DR

It appears that the rule that partitioning columns must form a subset of key columns of a unique index has been relaxed. It hasn't really. Instead, the reference partition uses the primary key on the parent of the foreign key as a proxy for the partitioning key.