Can I add a partition group to a partitioned table ?

bmurrell

I'm using v12.
I have a table partitioned by an expression on a date so it generates YYYYMMDD.
I didn't add a partition group originally. How can I add that afterwards?
The syntax for adding a partition group is after the partition statement implying it'll repartition the whole table (by the same formula) as well.

marcothesane

Do you mean this?

Before:

SQL>select export_objects('','public.supplier_scd',false)
export_objects
CREATE TABLE public.supplier_scd
(
    spplr_key int NOT NULL DEFAULT hash(supplier_scd.spplr_id, supplier_scd.spplr_from_dt),
    spplr_id int NOT NULL,
    spplr_from_dt date NOT NULL,
    spplr_to_dt date NOT NULL,
    spplr_is_hist boolean NOT NULL,
    spplr_ins_ts timestamp NOT NULL,
    spplr_upd_ts timestamp NOT NULL,
    spplr_name varchar(30) NOT NULL,
    spplr_org varchar(30),
    CONSTRAINT d_spplr_scd_pk PRIMARY KEY (spplr_key) DISABLED
)
PARTITION BY (supplier_scd.spplr_from_dt);
[. . .]

Then:

ALTER TABLE public.supplier_scd
PARTITION BY spplr_from_dt
  GROUP BY CALENDAR_HIERARCHY_DAY(spplr_from_dt,2,2)
REORGANIZE;

Leading to:
SQL> SELECT EXPORT_OBJECTS('','public.spplr_scd',FALSE);
CREATE TABLE public.supplier_scd
(
spplr_key int NOT NULL DEFAULT hash(supplier_scd.spplr_id, supplier_scd.spplr_from_dt),
spplr_id int NOT NULL,
spplr_from_dt date NOT NULL,
spplr_to_dt date NOT NULL,
spplr_is_hist boolean NOT NULL,
spplr_ins_ts timestamp NOT NULL,
spplr_upd_ts timestamp NOT NULL,
spplr_name varchar(30) NOT NULL,
spplr_org varchar(30),
CONSTRAINT d_spplr_scd_pk PRIMARY KEY (spplr_key) DISABLED
)
PARTITION BY (supplier_scd.spplr_from_dt)
GROUP BY (
CASE
WHEN ("datediff"('year', supplier_scd.spplr_from_dt, ((now())::timestamptz(6))::date) >= 2)
THEN (date_trunc('year', supplier_scd.spplr_from_dt))::date
WHEN ("datediff"('month', supplier_scd.spplr_from_dt, ((now())::timestamptz(6))::date) >= 2)
THEN (date_trunc('month', supplier_scd.spplr_from_dt))::date
ELSE supplier_scd.spplr_from_dt
END
);
```

bmurrell

Correct. Current table does not have GROUP BY but want to add it, using same partitioning scheme.
I'm not sure if under the hood it'll 1) rebuild everything, 2) re-validate all rows, or 3) just apply the metadata change, possibly followed by mergeouts to combine into groups.

marcothesane

The table data is not aggregated.
I use „2,2“ as 2nd and 3rd parameters . This keeps all partition keys(dates), but groups all data older than 2 months into ROS containers for 1 month‘s worth of data, and all data older than 2 years into ROS containers with 1 year‘s worth of data.