Partitioning problem + question?

EleanorWyatt

Hi Experts!
I have been trying to partition a billion rows table according to time by CALENDAR_HIERARCHY_DAY algorithm.omegle xender
when I run the statement:
ALTER TABLE public.spins
PARTITION BY time::DATE
GROUP BY CALENDAR_HIERARCHY_DAY(time::DATE, 2, 1) REORGANIZE;

I get the error:
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"

When I try to drop the projection (I don't really need it..) I get:

WARNING 7000: The projection 'spins_pk' was used to enforce the enabled key constraint 'pk', and may be regenerated to validate a DML statement on the base table

how can I workaround that issue?should I add the column time to the pk projection?

And a general partitioning question
If I want my partitions to be as follows: 24 hours - by hours, 7 days , 1 month, 3 month, 1 year..
what is the group by clause needed for the
GROUP BY CALENDAR_HIERARCHY algorithm.

Many thanks!!

Jim_Knicely

@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