0

I’ve made an attempt to create partition on test table using virtual column. This approach is working good for PARENT or standalone tables. However, I cannot create REFERENCE partition on CHILD table if the PARENT table is PARTITIONED using virtual column. I get the following error on create table of CHILD table

ORA-14659: Partitioning method of the parent table is not supported

Oracle Version details: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production

Please find the script below.

--######################PARENT TABLE###########################################
DROP TABLE BILL_HEADER_TST;

CREATE TABLE BILL_HEADER_TST
(
   BILL_HDR_SID   NUMBER (30) NOT NULL,
   TCN                VARCHAR2 (21 BYTE) NOT NULL,
   TCN_DATE           DATE,
   PROGRAM_CID        NUMBER,
   CONSTRAINT XPKBILL_HEADER_TST PRIMARY KEY (BILL_HDR_SID),
   PARTN_KEY          NUMBER

                            AS (   PROGRAM_CID
                                || TO_NUMBER (TO_CHAR (TCN_DATE, 'YYYYMM')))
                            VIRTUAL
)
PARTITION BY LIST (PARTN_KEY)   AUTOMATIC (PARTITION PDEFAULT VALUES (1201401));


------------------LOCAL INDEXES------------------------------------------------

CREATE INDEX XIE33BILL_HEADER_TST
   ON BILL_HEADER_TST (TCN_DATE)
   LOCAL;


CREATE INDEX XIE38BILL_HEADER_TST
   ON BILL_HEADER_TST (PROGRAM_CID)
   LOCAL;

---------------------INDEXES---------------------------------------------------
CREATE UNIQUE INDEX XAK1BILL_HEADER_TST
   ON BILL_HEADER_TST (TCN)
   LOGGING
   NOPARALLEL;


--#############CHILD TABLE#####################################################   
DROP TABLE BILL_LINE_TST;

CREATE TABLE BILL_LINE_TST
(
   BILL_LINE_SID                 NUMBER (30) NOT NULL,
   BILL_HDR_SID               NUMBER (30) NOT NULL,
   CLM_TYPE_CID                   NUMBER (3),
   PROGRAM_CID                    NUMBER,
   CONSTRAINT XPKBILL_LINE_TST PRIMARY KEY (BILL_LINE_SID),
   CONSTRAINT XFK17_BILL_LINE_TST FOREIGN KEY
      (BILL_HDR_SID)
       REFERENCES BILL_HEADER_TST (BILL_HDR_SID) ON DELETE CASCADE
)
PARTITION BY REFERENCE (XFK17_BILL_LINE_TST)
ENABLE ROW MOVEMENT;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
AJORA
  • 27
  • 5
  • I was able to create using `INTERVAL` instead of `AUTOMATIC` Check the last line of the CREATE TABLE BILL_HEADER_TST `PARTITION BY LIST (PARTN_KEY) INTERVAL (PARTITION PDEFAULT VALUES (1201401));` – AJORA Nov 22 '19 at 03:05

1 Answers1

0

From the SQL Language manual

Automatic list partitioning is subject to the restrictions listed in "Restrictions on List Partitioning". The following additional restrictions apply:

  • An automatic list-partitioned table must have at least one partition when created. Because new partitions are automatically created for new, and unknown, partitioning key values, an automatic list-partitioned table cannot have a DEFAULT partition.
  • Automatic list partitioning is not supported for index-organized tables or external tables.
  • Automatic list partitioning is not supported for tables containing varray columns.
  • You cannot create a local domain index on an automatic list-partitioned table. You can create a global domain index on an automatic list-partitioned table.
  • An automatic list-partitioned table cannot be a child table or a parent table for reference partitioning.
  • Automatic list partitioning is not supported at the subpartition level.
Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • Thanks Connor. I believe your point says that scenario I'm trying is not supported. **An automatic list-partitioned table cannot be a child table or a parent table for reference partitioning** Is there any workaround to achieve this? – AJORA Nov 10 '19 at 22:12
  • I think you'd need to "home grow" it, ie, bring the parent attribute down into the child table, and use conventional partitioning. You'll still all the benefits of partition-wise joins etc. – Connor McDonald Nov 18 '19 at 08:56