So I am able work out partition by reference with the DDL below, but can seem to figure out how to also get IOT too. The examples with IOT I have seen needed the PK defined in the create table DDL. Since my solution for making the PK on the child table with partition local required me building the PK as an alter. I cannot seem to figure out how to get both.
CREATE TABLE orders
( ORD_ID INTEGER GENERATED ALWAYS AS IDENTITY,
order_date DATE,
order_mode VARCHAR2(500)
)
partition by range (ORD_ID) interval (100)
(partition empty values less than (1)
)
;
CREATE UNIQUE INDEX PK_ORDERS_PK ON ORDERS (ORD_ID) local ;
ALTER TABLE ORDERS ADD CONSTRAINT "PK_ORDERS_PK" PRIMARY KEY (ORD_ID) ;
CREATE TABLE ORDER_ITEMS
( order_items_ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
ORD_ID INTEGER NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT FK_order_items_ORD_ID
FOREIGN KEY(ORD_ID) REFERENCES orders(ORD_ID)
)
PARTITION BY REFERENCE(FK_order_items_ORD_ID);
CREATE UNIQUE INDEX PK_ORDER_ITEMS_PK ON ORDER_ITEMS (ORD_ID,order_items_ID) local ;
ALTER TABLE ORDER_ITEMS ADD CONSTRAINT "PK_ORDER_ITEMS_PK" PRIMARY KEY (ORD_ID,order_items_ID)
USING INDEX "PK_ORDER_ITEMS_PK" ENABLE;