Hi Team,
i want to create a table with monthly partition on it so that i can make purging easy by dropping partitions that are not needed. i also want to avoid rebuild of primary key index after dropping partition, how to do it ?
can primary key index be local ?
following is create table statement
CREATE TABLE large_table (
auto_id NUMBER GENERATED AS IDENTITY (START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
tran_id NUMBER,
created TIMESTAMP(6) DEFAULT systimestamp,
CONSTRAINT large_table_hits_pk PRIMARY KEY (auto_id)
)
PARTITION BY RANGE (created)
INTERVAL (INTERVAL '1' MONTH)
(
PARTITION p0 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
);
Yes you can, but requires a compromise. You need to add CREATED as a column in the primary key, eg
SQL> CREATE TABLE large_table (
2 auto_id NUMBER GENERATED AS IDENTITY NOT NULL,
3 tran_id NUMBER,
4 created TIMESTAMP(6) DEFAULT systimestamp,
5 CONSTRAINT large_table_hits_pk PRIMARY KEY (auto_id,created) using index local
6 )
7 PARTITION BY RANGE (created)
8 INTERVAL (INTERVAL '1' MONTH)
9 (
10 PARTITION p0 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
11 );
Table created.This obviously has implications for your applications in how they access the data, what keys they store etc etc, but it can be done.
Another option I've seen used, is a secondary table, because your LARGE_TABLE above is more likely to be
CREATE TABLE large_table (
auto_id NUMBER GENERATED AS IDENTITY NOT NULL,
tran_id NUMBER,
created TIMESTAMP(6) DEFAULT systimestamp,
..
..
.. LOTS OF OTHER COLUMNS
..
..
CONSTRAINT large_table_hits_pk PRIMARY KEY (auto_id,created) using index local
)
PARTITION BY RANGE (created)
INTERVAL (INTERVAL '1' MONTH)
(
PARTITION p0 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
);
An additional table could be just:
create table LARGE_KEYS
auto_id number,
created timestamp(6),
constraint LARGE_KEYS_PK primary key ( auto_id)
)
organization index;
This table is non-partitioned but never needs archiving - its the link between and ID and its CREATED time.
Thus your apps just need to ID as per normal. The ID uses LARGE_KEYS to get the CREATED value, which is then used to mine into LARGE_TABLE for the rest of the data.