Resolved: Partitioned index for primary key on partitioned table

Question:

I want to create partitioned table:

            create table audit
            (
                id             number(38,0) not null enable,
                audit_time     timestamp(6),
                description    varchar2(100 byte),
                constraint pk_audit primary key (id)
            )
            partition by range (audit_time)
                interval(numtoyminterval(1, 'month'))
            (
                partition low_p values less than (timestamp' 2010-01-01 00:00:00')
            );

            create index audit_idx on audit(audit_time) local;

And there will be about 300 millions row. I can’t find clear answer about partitioning of PK for partitioned table.
My questions are:

Should I make a partitioned index for primary key?
It must be globally partitioned or local?
It must be hash partitioned?
How to know how many partitions must be for that index?

It should be something like this:

CREATE INDEX audit_unq
ON audit(id)
GLOBAL PARTITION BY HASH (id)
( PARTITION p1
, PARTITION p2
, PARTITION p3
, PARTITION p4
);

or not?)
What is best practice with pk of partitoned table?

Answer:

You can create UNIQUE (or PRIMARY KEY) index LOCAL however, then the partition key must be part of the index, i.e. you need to create a composite primary key:

create table audit
(
    id             number(38,0) not null enable,
    audit_time     timestamp(6),
    description    varchar2(100 byte)
)
partition by range (audit_time)
    interval(numtoyminterval(1, 'month'))
(
    partition low_p values less than (timestamp' 2010-01-01 00:00:00')
);

ALTER TABLE audit ADD (CONSTRAINT audit_PK PRIMARY KEY (id, audit_time) USING INDEX LOCAL);

A UNIQUE LOCAL index is not possible, unless partition key column is included.
An index like

CREATE INDEX audit_unq ON audit(id) GLOBAL PARTITION BY HASH (id) ( PARTITION p1, ...);

is possible. However the index partitions will be different to the table partitions. I don’t see any purpose of this index. You don’t gain any performance by Partition Pruning and when you drop/truncate a partition then you have to rebuild index audit_unq. So you don’t get any benefit, actually it combines the drawbacks of global and local indexes.

If you have better answer, please add a comment about this, thank you!

Resolved: Partitioned index for primary key on partitioned table

Resolved: Why doesn’t stringstream consume output during hex formatting?

Resolved: CMake path for python3 libraries doesn’t change (windows 10)

Resolved: GLMM with quasi-Poisson distribution

Leave A Reply