Partitioning in Oracle using custom functions

Question

I'm trying to partition my table using the ID column such that all even ID's should go in partition_1 and odd ID's should go in partition_2. The only closest thing that met my needs was virtual columns.

CREATE TABLE sales
(
 id       NUMBER(6) NOT NULL,
 mod_id AS (MOD(id, 2))
);
PARTITION BY RANGE (mod_id)
(
 PARTITION mod_id VALUES LESS THAN(1),
 PARTITION mod_id VALUES LESS THAN(2),
)

Is there a better way than this?

this is my first time partitioning, how do I know the partition in which a record resides? — ffff, Sep 28 '16 at 12:00
things aren't bad, but while inserting i have to specify all the columns (except the virtual column). — ffff, Sep 28 '16 at 12:04
This seems like an unusual way to partition. What are you trying to achieve with this partitioning scheme? — Jon Heller, Sep 29 '16 at 00:10

score 0 · Answer 1 · answered Sep 28 '16 at 12:29

As you are using MOD function to calculate mod_id, your column would have only 2 values (0 & 1), In that case you can go with LIST partition as below.

CREATE TABLE sales
(
 id       NUMBER(6) NOT NULL,
 mod_id AS (MOD(id, 2))
)
PARTITION BY LIST (mod_id)
      (PARTITION mod_id0 VALUES (0),
       PARTITION mod_id1 VALUES (1));

It is certain that records with odd id will to partition mod_id1 and even into mod_id0.
You can verify it using below query:

select * from sales partition (mod_id1);

Partitioning in Oracle using custom functions

1 Answers1