Skip to Main Content
  • Questions
  • Is it possible to do both partition by reference with local partition PK/Index and have a index ordered table (IOT)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ed.

Asked: February 18, 2025 - 8:25 pm UTC

Last updated: February 19, 2025 - 3:18 pm UTC

Version: 19

Viewed 1000+ times

You Asked

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;

and Chris said...

There are several problems here:

- Interval partitioning is unsupported on index-organized tables
- Reference partitioning is unsupported when the parent table is index-organized
- You can't alter a heap-organized table to become index-organized; this has to be defined when creating the table

So no: you can't do this!

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.