Can I add a partition group to a partitioned table ?

Answers

• Options

  marcothesane - Select Field - Administrator

  November 2023

  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
  );
  ```

  1
• Options

  bmurrell Community Edition User ✭

  November 2023

  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.

  0
• Options

  marcothesane - Select Field - Administrator

  November 2023

  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.

  0