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 indexThis 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 columnsWhat 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 4The 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.
@go-faster.co.uk on BlueSky
Posts
