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:
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?
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.