你的位置:首页 > 数据库

[数据库]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_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