Oracle 11G 虛擬列 Virtual Column

Oracle 11G 在表中引入了虛擬列，虛擬列是一個表示式，在執行時計算，不儲存在資料庫中，不能更新虛擬列的值。

定義一個虛擬列的語法：

column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]

1.虛擬列可以用在select，update，delete語句的where條件中，但是不能用於DML語句
2.可以基於虛擬列來做分割槽
3. 可以在虛擬列上建索引，oracle的函式索引就類似。
4. 可以在虛擬列上建約束

建立一個帶虛擬列的表：

CREATE TABLE EMPLOYEE
(
empl_id NUMBER,
empl_nm VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2),
total_sal NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*12 + bonus)
);

total_sal就是一個虛擬列

檢視下錶定義內容：

SELECT column_name, data_type, data_length, data_default, virtual_column
FROM user_tab_cols
WHERE table_name = 'EMPLOYEE';

COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT | VIRTUAL_COLUMN
EMPL_ID | NUMBER | 22 | null | NO
EMPL_NM | VARCHAR2 | 50 | null | NO
MONTHLY_SAL | NUMBER | 22 | null | NO
BONUS | NUMBER | 22 | null | NO
TOTAL_SAL | NUMBER | 22 | "MONTHLY_SAL"*12+"BONUS" | YES

virtual_column是yes，表示是虛擬列，我們也可以在建表語句加上VIRTUAL，顯示宣告為虛擬列

DROP TABLE EMPLOYEE PURGE;

CREATE OR REPLACE FUNCTION get_empl_total_sal ( p_monthly_sal NUMBER,
p_bonus NUMBER)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
RETURN p_monthly_sal * 12 + p_bonus;
END;

CREATE TABLE EMPLOYEE
(empl_id NUMBER,
empl_nm VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2),
total_sal NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus)) VIRTUAL
);

我們可以在虛擬列上建立索引

CREATE INDEX idx_total_sal ON employee(total_sal);

SELECT index_name, index_type
FROM user_indexes
WHERE table_name = 'EMPLOYEE';

INDEX_NAME INDEX_TYPE
IDX_TOTAL_SAL FUNCTION-BASED NORMAL

這個函式也是表定義內容

DROP FUNCTION get_empl_total_sal;

SELECT * FROM employee;
*
Error at line 0
ORA-00904: "schema"."GET_EMPL_TOTAL_SAL": invalid identifier

可以新增一個虛擬列

DROP TABLE EMPLOYEE PURGE;

CREATE TABLE EMPLOYEE
(empl_id NUMBER,
empl_nm VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2)
);

ALTER TABLE EMPLOYEE ADD (total_sal AS (monthly_sal * 12 + bonus));

這個新列的型別時不確定的，是根據表示式來推斷的。

INSERT INTO employee (empl_id, empl_nm, monthly_sal, bonus)
WITH DATA AS
(SELECT 100 empl_id, 'AAA' empl_nm, 20000 monthly_sal, 3000 bonus
FROM DUAL
UNION
SELECT 200, 'BBB', 12000, 2000
FROM DUAL
UNION
SELECT 300, 'CCC', 32100, 1000
FROM DUAL
UNION
SELECT 400, 'DDD', 24300, 5000
FROM DUAL
UNION
SELECT 500, 'EEE', 12300, 8000
FROM DUAL)
SELECT *
FROM DATA;

SELECT * FROM employee;

EMPL_ID | EMPL_NM | MONTHLY_SAL | BONUS | TOTAL_SAL
100 | AAA | 20000 | 3000 | 243000
200 | BBB | 12000 | 2000 | 146000
300 | CCC | 32100 | 1000 | 386200
400 | DDD | 24300 | 5000 | 296600
500 | EEE | 12300 | 8000 | 155600

在虛擬列上執行update語句是不允許的

UPDATE employee
SET total_sal = 2000;

ORA-54017: UPDATE operation disallowed on virtual columns

能夠從虛擬列上收集表的統計資訊

EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'EMPLOYEE');

SELECT column_name, num_distinct,
display_raw (low_value, data_type) low_value,
display_raw (high_value, data_type) high_value
FROM dba_tab_cols
WHERE table_name = 'EMPLOYEE';

COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE
TOTAL_SAL | 5 | 146000 | 386200
BONUS | 5 | 1000 | 8000
MONTHLY_SAL | 5 | 12000 | 32100
EMPL_NM | 5 | AAA | EEE
EMPL_ID | 5 | 100 | 500

基於虛擬列的分割槽

DROP TABLE EMPLOYEE PURGE;

CREATE TABLE employee
(empl_id NUMBER,
empl_nm VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2),
total_sal NUMBER(10,2) AS (monthly_sal*12 + bonus)
)
PARTITION BY RANGE (total_sal)
(PARTITION sal_200000 VALUES LESS THAN (200000),
PARTITION sal_400000 VALUES LESS THAN (400000),
PARTITION sal_600000 VALUES LESS THAN (600000),
PARTITION sal_800000 VALUES LESS THAN (800000),
PARTITION sal_default VALUES LESS THAN (MAXVALUE));

INSERT INTO employee (empl_id, empl_nm, monthly_sal, bonus)
WITH DATA AS
(SELECT 100 empl_id, 'AAA' empl_nm, 20000 monthly_sal, 3000 bonus
FROM DUAL
UNION
SELECT 200, 'BBB', 12000, 2000
FROM DUAL
UNION
SELECT 300, 'CCC', 32100, 1000
FROM DUAL
UNION
SELECT 400, 'DDD', 24300, 5000
FROM DUAL
UNION
SELECT 500, 'EEE', 12300, 8000
FROM DUAL)
SELECT *
FROM DATA;

EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'EMPLOYEE',granularity => 'PARTITION');

SELECT table_name, partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'EMPLOYEE'
ORDER BY partition_name;

TABLE_NAME | PARTITION_NAME | NUM_ROWS
EMPLOYEE | SAL_200000 | 2
EMPLOYEE | SAL_400000 | 3
EMPLOYEE | SAL_600000 | 0
EMPLOYEE | SAL_800000 | 0
EMPLOYEE | SAL_DEFAULT | 0

在分割槽情況下，不能更新虛擬列引用的列

UPDATE employee
SET monthly_sal = 30000
WHERE empl_id = 500;

ORA-14402: updating partition key column would cause a partition change

如果在分割槽情況能夠更新，則需要設定ENABLE ROW MOVEMENT

ALTER TABLE employee ENABLE ROW MOVEMENT;

UPDATE employee
SET monthly_sal = 80000
WHERE empl_id = 500;

1 row updated.

不能基於函式表示式的虛擬列上的分割槽

CREATE TABLE employee_new
(empl_id NUMBER,
empl_nm VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2),
total_sal NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus))
)
PARTITION BY RANGE (total_sal)
(PARTITION sal_200000 VALUES LESS THAN (200000),
PARTITION sal_400000 VALUES LESS THAN (400000),
PARTITION sal_600000 VALUES LESS THAN (600000),
PARTITION sal_800000 VALUES LESS THAN (800000),
PARTITION sal_default VALUES LESS THAN (MAXVALUE));

ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns

原文連結：http://blog.csdn.net/zhongweijian/article/details/9008067

Oracle11G 虛擬列 Virtual Column使用

相關文章