As of Oracle Database 11g it is possible to use a virtual column as partition key. In this post I do not want to discuss how it works and whether this is good or not… Instead, I would like to show you that the feature might lead to wrong results.

First of all, I would like to show you a test where everything works fine. For that purpose, let’s create a table (notice the virtual column n2), insert one row into it, and gather the object statistics:

SQL> CREATE TABLE t (
  2    n1 NUMBER,
  3    n2 AS (CASE n1 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 0 END) VIRTUAL
  4  )
  5  PARTITION BY LIST (n2) (
  6    PARTITION zero VALUES (0),
  7    PARTITION one VALUES (1),
  8    PARTITION two VALUES (2)
  9  )
 10  ENABLE ROW MOVEMENT;

SQL> INSERT INTO t (n1) VALUES (1);

SQL> COMMIT;

SQL> execute dbms_stats.gather_table_stats(user,'t')

The aim of the following test is to check whether row movement works correctly. Hence, I update the column n1 to cause such a movement. To check whether row movement is performed or not, I display the content of the two involved partitions before and after the update statement. In addition, I also display the rowids (because of the movement the row should get a new rowid).

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

no rows selected

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

ROWID                      N1         N2
------------------ ---------- ----------
AAAE89AAEAAAAGNAAA          1          1

SQL> UPDATE t SET n1 = 3;

SQL> COMMIT;

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

ROWID                      N1         N2
------------------ ---------- ----------
AAAE88AAEAAAAF9AAA          3          0

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

no rows selected

The previous test was successful. Now, let me show you a situation that leads to wrong results :-(

To reproduce the bug I basically execute the same operations as before. The only difference is that seven columns are added before the columns n1 and n2 in the table. Hence, the test table is recreated with the following statements:

SQL> DROP TABLE t PURGE;

SQL> CREATE TABLE t (
  2    d1 NUMBER,
  3    d2 NUMBER,
  4    d3 NUMBER,
  5    d4 NUMBER,
  6    d5 NUMBER,
  7    d6 NUMBER,
  8    d7 NUMBER,
  9    n1 NUMBER,
 10    n2 AS (CASE n1 WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 0 END) VIRTUAL
 11  )
 12  PARTITION BY LIST (n2) (
 13    PARTITION zero VALUES (0),
 14    PARTITION one VALUES (1),
 15    PARTITION two VALUES (2)
 16  )
 17  ENABLE ROW MOVEMENT;

SQL> INSERT INTO t (n1) VALUES (1);

SQL> COMMIT;

SQL> execute dbms_stats.gather_table_stats(user,'t')

As before, I update the row to cause the movement and display the content of the two involved partitions before and after doing it.

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

no rows selected

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

ROWID                      N1         N2
------------------ ---------- ----------
AAAE9BAAEAAAAGNAAA          1          1

SQL> UPDATE t SET n1 = 3;

SQL> COMMIT;

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

no rows selected

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

ROWID                      N1         N2
------------------ ---------- ----------
AAAE9BAAEAAAAGNAAA          3          0

As you can see, the two queries after the update statement return wrong results. Also the rowid is the same. Hence, row movement was not performed. It goes without saying that also other queries might return wrong results. An example is the following:

SQL> SELECT rowid, n1, n2 FROM t WHERE n2 = 1;

ROWID                      N1         N2
------------------ ---------- ----------
AAAE9BAAEAAAAGNAAA          3          0

By playing around with the number of columns and position of the columns n1 and n2, I found out that depending on the situation you might have correct results or wrong results.

Since I was able to reproduce the problem with several databases (both 11.1.0.6 and 11.1.0.7), last Friday I opened a service request. Now the issue is tracked as bug# 8258501.

Update 2013-06-19: 11.2.0.2 introduces a fix for this bug.