如何在Oracle中的间隔分区表中存储NULL值? [英] How to store NULL values in an interval partitioned table in Oracle?
本文介绍了如何在Oracle中的间隔分区表中存储NULL值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
We use partitioning to be able to delete old data (Oracle calls this ILM). There is a column with the "best before date", after which the partition will be dropped.
为避免必须手动(或按脚本)添加分区,我们使用间隔分区:
To avoid having to add partitions manually (or per script), we use interval partitioning:
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
:
... which works fine, but we cannot leave the partitioning date to NULL
:
INSERT INTO my_table(id, t) VALUES (2, NULL);
-- ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
如何解决?
推荐答案
例如,您可以这样做:
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'))
,这取决于哪种逻辑更适合您.
Perhaps you can also use GENERATED ALWAYS AS (COALESCE(t, TIMESTAMP '1900-01-01 00:00:00'))
depending what fits better to your logic.
这篇关于如何在Oracle中的间隔分区表中存储NULL值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文