Table Partitioning on Multiple columns different types

Question

I would like to create a partition table in Oracle SQL DEVELOPER, using two columns with different types? I want to stock in the partition table all data where:

creation_date <'2021' and task_status='done'

But I did not find a way on how to create it because I need to choose only one column to create a partition table, Or in the case of partition with multiple columns , the columns must have the same datatype.

Any help would be appreciated!

Answer 1

You can create subpartitions, for example like this:

CREATE TABLE ...
(
 creation_date TIMESTAMP(0) NOT NULL,
 task_status VARCHAR2(20 CHAR) NOT NULL,
 ...
)
PARTITION BY RANGE (creation_date)
INTERVAL (INTERVAL '1' MONTH)
SUBPARTITION BY LIST (task_status)
SUBPARTITION TEMPLATE (
 SUBPARTITION P_DONE VALUES ('done'),
 SUBPARTITION P_PENDING VALUES ('pending','waiting'),
 SUBPARTITION P_OTHERS VALUES (DEFAULT)
)
(
 PARTITION P_INITIAL VALUES LESS THAN (TIMESTAMP '2024-01-01 00:00:00') 
)
ENABLE ROW MOVEMENT;

Monthly partitions will be created automatically when you insert any data.