虚拟列分区可能返回错误结果Virtual Column-Based Partition-Chris译客

TrackBack: http://antognini.ch/2009/02/virtual-column-based-partitioning-might-lead-to-wrong-results/

Oracle 11g(11.1.0.6 和 11.1.0.7),在虚拟列分区表里面,有许多列,当虚拟列或者源数据列排在后面时,修改数据和查询数据会产生意想不到的错误结果.
出错的情况是随机的, 比如数据被放进了错误的分区, 或者查询数据,返回错误结果

这里是测试用例:

SQL>
drop TABLE t;
CREATE TABLE t (
d1 NUMBER,
d2 NUMBER,
d3 NUMBER,
d4 NUMBER,
d5 NUMBER,
d6 NUMBER,
d7 NUMBER,
n1 NUMBER,
n2 AS (CASE n1 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 0 END) VIRTUAL
)
PARTITION BY LIST (n2) (
PARTITION zero VALUES (0),
PARTITION one VALUES (1),
PARTITION two VALUES (2)
)
ENABLE ROW MOVEMENT;

INSERT INTO t (n1) VALUES (1);
commit;
UPDATE t SET n1 = 3;
commit;

SELECT rowid, n1, n2 FROM t PARTITION (zero);
SELECT rowid, n1, n2 FROM t PARTITION (one);

数据被放进了错误的分区.
SQL> SELECT rowid, n1, n2 FROM t PARTITION (one);

ROWID N1 N2
------------------ ---------- ----------
AAAd7XAAEAAABXCAAA 3 0

查询数据,返回错误结果
SQL> SELECT rowid, n1, n2 FROM t WHERE n2 = 1;

ROWID N1 N2
------------------ ---------- ----------
AAAd7XAAEAAABXCAAA 3 0

昨天在内港Delta酒店参加领导能力培训,南望华盛顿州,奥林匹亚山.