如何在甲骨文的间隔分区表中存储 NULL 值?

Question

我们使用分区来删除旧数据(Oracle称之为ILM(。 有一列带有"此日期前最佳"，之后分区将被删除。

为了避免手动(或按脚本(添加分区，我们使用间隔分区：

CREATE TABLE my_table (
id NUMBER NOT NULL PRIMARY KEY,
--... other columns ...
t DATE
) PARTITION BY RANGE (t) INTERVAL(NUMTODSINTERVAL(7, 'DAY')) (
PARTITION PRE2018 VALUES LESS THAN (DATE '2018-01-01')
);
INSERT INTO my_table(id, t) VALUES (1, SYSDATE);
-- 1 row inserted.

。这工作正常，但我们不能将分区日期留给NULL：

INSERT INTO my_table(id, t) VALUES (2, NULL);
-- ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

如何解决这个问题？

Answer 1

您可以这样做，例如：

CREATE TABLE my_table (
ID NUMBER NOT NULL PRIMARY KEY,
--... other columns ...
t TIMESTAMP(0),
PARTITION_KEY TIMESTAMP(0) GENERATED ALWAYS AS (COALESCE(t, TIMESTAMP '2999-01-01 00:00:00')) VIRTUAL
)
PARTITION BY RANGE (PARTITION_KEY) INTERVAL( NUMTODSINTERVAL(7, 'DAY')) ( 
PARTITION PRE2018 VALUES LESS THAN (TIMESTAMP '2018-01-01 00:00:00')
);

像PARTITION FOR (TIMESTAMP '2999-01-01 00:00:00')这样解决它应该很简单

也许你也可以使用GENERATED ALWAYS AS (COALESCE(t, TIMESTAMP '1900-01-01 00:00:00'))，这取决于什么更适合你的逻辑。

Answer 2

如果键值为 NULL，则使用插入时触发器，它将插入默认值或 SYSDATE