oracle 11g 虚拟分区,11G的虚拟列分区测试

在11G中，ORACLE引入了虚拟列的概念，正如其名，虚拟列实际上在物理上是不存在的列，

在查询的时候，ORACLE才开始计算。

下面是创建一个带有虚拟列的表的简单例子。

SQL> CREATE TABLE VIRTUAL_COLUNN_TEST

2  (ID INT,

3  NAME CHAR(10),

4  SEX CHAR(1),

5  VIRTUAL_NAME CHAR(10) GENERATED ALWAYS AS (UPPER(NAME)) VIRTUAL

6  )

7  /

Table created.

虚拟列是不能插入数据的，虚拟列是通过其他列或者其他虚拟列计算出来的。

SQL> INSERT INTO VIRTUAL_COLUNN_TEST VALUES(1,'yansp','1','YANSP');

INSERT INTO VIRTUAL_COLUNN_TEST VALUES(1,'yansp','1','YANSP')

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

SQL>  INSERT INTO VIRTUAL_COLUNN_TEST(ID,NAME,SEX)  VALUES(1,'yansp','1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM VIRTUAL_COLUNN_TEST;

ID NAME       S VIRTUAL_NA

---------- ---------- - ----------

1 yansp      1 YANSP

由于虚拟列的存在，ORACLE引入了虚拟列的分区。虚拟列分区和其他分区方式没什么太大

区别，只不过分区键是用的虚拟列。

SQL> CREATE TABLE VIRTUAL_COLUMN_PARTITION

2  (

3    ID           INT,

4    NAME         CHAR(16),

5    BIRTH        CHAR(10),

6   V_BIRTH_YEAR CHAR(4) GENERATED ALWAYS AS (SUBSTRB(BIRTH,1,4)) VIRTUAL  7  )

8  PARTITION BY RANGE(V_BIRTH_YEAR)

9  (

10   PARTITION P70         VALUES LESS THAN ('1980'),

11   PARTITION P80         VALUES LESS THAN ('1990'),

12   PARTITION P90         VALUES LESS THAN ('2000'),

13   PARTITION PDEFAULT VALUES LESS THAN(MAXVALUE)

14  )

15  /

Table created.

虚拟列的分区类型和原有的分区类型没什么区别。

SQL> SELECT TABLE_NAME,PARTITIONING_TYPE FROM USER_PART_TABLES;

TABLE_NAME                     PARTITION

------------------------------ ---------

A                              RANGE

CHILDREN                       REFERENCE

LIST_LIST                      LIST

SYSTEM_PARTITION               SYSTEM

VIRTUAL_COLUMN_PARTITION       RANGE

SQL> INSERT INTO VIRTUAL_COLUMN_PARTITION(ID,NAME,BIRTH) VALUES(1,'YANSHOUPENG','1986/08/24');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM VIRTUAL_COLUMN_PARTITION;

ID NAME             BIRTH      V_BI

---------- ---------------- ---------- ----

1 YANSHOUPENG      1986/08/24 1986

SQL>  SELECT * FROM VIRTUAL_COLUMN_PARTITION PARTITION (P80);

ID NAME             BIRTH      V_BI

---------- ---------------- ---------- ----

1 YANSHOUPENG      1986/08/24 1986