Oracle 11g 虛擬列 Virtual Column介紹

標籤：

Oracle 11G 虛擬列 Virtual ColumnOracle 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_COLUMNEMPL_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 NUMBERDETERMINISTICISBEGIN   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 0ORA-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_SAL100     | AAA     | 20000       | 3000  | 243000200     | BBB     | 12000       | 2000  | 146000300     | CCC     | 32100       | 1000  | 386200400     | DDD     | 24300       | 5000  | 296600500     | 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_VALUETOTAL_SAL   | 5            | 146000    | 386200BONUS       | 5            | 1000      | 8000MONTHLY_SAL | 5            | 12000     | 32100EMPL_NM     | 5            | AAA       | EEEEMPL_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_rowsFROM user_tab_partitions   WHERE table_name = ‘EMPLOYEE‘ORDER BY partition_name; TABLE_NAME | PARTITION_NAME | NUM_ROWSEMPLOYEE   | SAL_200000     | 2EMPLOYEE   | SAL_400000     | 3EMPLOYEE   | SAL_600000     | 0EMPLOYEE   | SAL_800000     | 0EMPLOYEE   | 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 MOVEMENTALTER 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

 

Oracle 11g 虛擬列 Virtual Column介紹