Oracle 11G虚拟列Virtual Column介绍(二)
mployee;
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)