Partitioning problem + question?

Answers

• Jim_Knicely - Select Field - Administrator

  June 2021

  @EleanorWyatt - You can drop the PK constraint, add the partition and then add the PK constraint back.

  Example:

  verticademos=> CREATE TABLE spins (c2 INT, c1 INT NOT NULL, time timestamp);
  CREATE TABLE
  
  verticademos=> INSERT INTO spins SELECT 1, 1, SYSDATE;
   OUTPUT
  --------
        1
  (1 row)
  
  verticademos=> ALTER TABLE spins ADD CONSTRAINT spins_pk PRIMARY KEY(c1) ENABLED;
  ALTER TABLE
  
  verticademos=> SELECT export_objects('', 'spins');
                                                                                                                                                                                                                                                                                                                                export_objects                                                                                                                                                                                                
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
  
  CREATE TABLE public.spins
  (
      c2 int,
      c1 int NOT NULL,
      "time" timestamp,
      CONSTRAINT spins_pk PRIMARY KEY (c1) ENABLED
  );
  
  
  CREATE PROJECTION public.spins_super /*+basename(spins),createtype(L)*/
  (
   c2,
   c1,
   "time"
  )
  AS
   SELECT spins.c2,
          spins.c1,
          spins."time"
   FROM public.spins
   ORDER BY spins.c2,
            spins.c1,
            spins."time"
  SEGMENTED BY hash(spins.c2, spins.c1, spins."time") ALL NODES OFFSET 0;
  
  CREATE PROJECTION public.spins_pk /*+createtype(N)*/
  (
   c1
  )
  AS
   SELECT spins.c1
   FROM public.spins
   ORDER BY spins.c1
  SEGMENTED BY hash(spins.c1) ALL NODES OFFSET 0;
  
  
  SELECT MARK_DESIGN_KSAFE(0);
  
  (1 row)
  
  verticademos=> ALTER TABLE public.spins
  verticademos-> PARTITION BY time::DATE
  verticademos-> GROUP BY CALENDAR_HIERARCHY_DAY(time::DATE, 2, 1) REORGANIZE;
  ROLLBACK 2648:  Column time in PARTITION BY expression is not allowed, since it is not present in some projections
  HINT:  Projection "spins_pk" does not contain column "time"
                                                   ^
  verticademos=> ALTER TABLE public.spins DROP CONSTRAINT spins_pk;
  ALTER TABLE
  
  verticademos=> ALTER TABLE public.spins
  verticademos-> PARTITION BY time::DATE
  verticademos-> GROUP BY CALENDAR_HIERARCHY_DAY(time::DATE, 2, 1) REORGANIZE;
  NOTICE 8364:  The new partitioning scheme will produce partitions in 1 physical storage containers per projection
  WARNING 9249:  Using PARTITION expression that may result in NULL values
  HINT:  DML statements which produce NULL values for partition expressions will roll back
  NOTICE 4785:  Started background repartition table task
  ALTER TABLE
  
  verticademos=> ALTER TABLE spins ADD CONSTRAINT spins_pk PRIMARY KEY(c1) ENABLED;
  ALTER TABLE
  

  0