The enterprise and presentation layers of one of the VLDB Data Warehouse (DW) projects that I work on is date range partitioned. This allows us to easily create a sliding window of data that is as big as we can handle given the storage constraints. Because we do partition maintenance operations quite often, we don’t use any global indexes — all of our indexes are local prefix indexes. This impacts primary key index generation.

If the table is created with a primary key constraint


    CREATE TABLE mydata (
         part    TIMESTAMP NOT NULL,
         id        NUMBER NOT NULL,
         title    VARCHAR2(256)
     );
     ALTER TABLE mydata ADD
         (CONSTRAINT pk_mydata PRIMARY KEY(part,id));

Then what you get is a global non-partitioned primary key. One common piece of DW advice is to just not use primary keys at all, but this removes some of the self-documentation that exists in the schema that would be available to both the Cost Based Optimizer and any data modeling tools that might be used by a future DBA or programmer.

Here is what I have come up with that preserves the documentation and creates a LOCAL partitioned index for the primary key fields (Oracle 10g).


   CREATE TABLE mydata (
         part    TIMESTAMP NOT NULL,
         id        NUMBER NOT NULL,
         title    VARCHAR2(256)
     );
    ALTER TABLE mydata ADD
      (CONSTRAINT pk_mydata  PRIMARY KEY (part,id)
      DISABLE NOVALIDATE;
    ALTER TABLE mydata MODIFY CONSTRAINT pk_mydata RELY;
    CREATE INDEX mydata_pk_idx
         ON mydata(part,id) LOCAL
         COMPUTE STATISTICS PARALLEL
/

So now the primary key is there in the user_constraints table for everyone to find. It can be used by the optimizer if needed, but it is not enforced. The index created over the columns that I intend to function as primary key columns is created as a local index and each local index chunk lives in the partition with the data that it indexes. This allows the partition maintenance operations to slide the window forward one date range increment as needed without any indexes becoming unusable.