Oracle虚拟列分区测试

虚拟列分区是基于其他列进行虚拟计算的结果做分区，这个虚拟列不会实际存储值，是通过其他列计算而来，虚拟列丰富了Oracle分区键的选择。

CREATE TABLE t (
d1 NUMBER,
d2 NUMBER,
d3 NUMBER,
d4 NUMBER,
d5 NUMBER,
d6 NUMBER,
d7 NUMBER,
-- d8 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;


SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
D1 NUMBER
D2 NUMBER
D3 NUMBER
D4 NUMBER
D5 NUMBER
D6 NUMBER
D7 NUMBER
N1 NUMBER
N2 NUMBER

字段N2是虚拟字段，是通过字段N1的值计算而来，N1的值只有三个1，2，0，对应N1的值为1，2，其他。

下面插入数据
SQL> insert into t (n1) values(0);

1 row created.

SQL> insert into t (n1) values(1);

1 row created.

SQL> insert into t (n1) values(2);

1 row created.

SQL> insert into t (n1) values(3);

1 row created.

SQL> commit;

Commit complete.
查看分区分布
SQL>select table_name,partition_name,PARTITION_POSITION,TABLESPACE_NAME from user_tab_partitions where table_NAME='T'

TABLE_NAME PARTITION_ PARTITION_POSITION TABLESPACE_NAME
---------- ---------- ------------------ --------------------
T ZERO 1 USERS
T ONE 2 USERS
T TWO 3 USERS


数据查询结果
SQL> select * from t;

D1 D2 D3 D4 D5 D6 D7 N1 N2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0
3 0
1 1
2 2
数据在分区中的分布
SQL> select * from t partition(zero);

D1 D2 D3 D4 D5 D6 D7 N1 N2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0
3 0

SQL> select * from t partition(one);

D1 D2 D3 D4 D5 D6 D7 N1 N2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1

SQL> select * from t partition(two);

D1 D2 D3 D4 D5 D6 D7 N1 N2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2 2

经过查询验证数据确实按照虚拟列的值存储，并且虚拟列确实通过计算而来。

下面通过查询rowid来确定N2的值来自不同的行。
SQL> select rowid,n1,n2 from t partition(zero);

ROWID N1 N2
------------------ ---------- ----------
AAASPBAAHAAAAF8AAA 0 0
AAASPBAAHAAAAF8AAB 3 0

SQL> select rowid,n1 from t partition(zero);

ROWID N1
------------------ ----------
AAASPBAAHAAAAF8AAA 0
AAASPBAAHAAAAF8AAB 3

虚拟列可以像正常列一样做查询使用，但是不支持更新，虚拟列是通过其他列经过函数计算推导而来，这个
计算顺序无法页不必可逆。
SQL> SELECT rowid, n1, n2 FROM t WHERE n2 = 0;

ROWID N1 N2
------------------ ---------- ----------
AAASPBAAHAAAAF8AAA 0 0
AAASPBAAHAAAAF8AAB 3 0


SQL> update t set n2=0 where n2=1;
update t set n2=0 where n2=1
*
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual columns

支持删除
SQL> delete from t where n2=0;

2 rows deleted.

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

no rows selected
。

在实际生产中，我们确实有这样的需求，比如用户插入数据字段为date类型，但是由于数据量大需要每天做分区，所以此时就需要从日期字段中提取day的值，将数据放入分区表。Oracle虚拟列分区的功能确实增加了分区灵活度，更好的满足多样的业务需求。