原文链接:http:
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
<script type="text/javascript">
$(function () {
$('pre.prettyprint code').each(function () {
var lines = $(this).text().split('\n').length;
var $numbering = $('<ul/>').addClass('pre-numbering').hide();
$(this).addClass('has-numbering').parent().append($numbering);
for (i = 1; i <= lines; i++) {
$numbering.append($('<li/>').text(i));
};
$numbering.fadeIn(1700);
});
});
</script>
分享到:
相关推荐
oracle11g官方中文帮助 Oracle 11g:ORACLE ACTIVE DATA GUARD.pdf Oracle Database 11g 高可用性.pdf Oracle 数据库 11g 中的分区.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g...
Oracle 11g 官方中文文档 包括一下部分文档: Oracle 11g:ORACLE ACTIVE DATA GUARD.pdf Oracle Database 11g 高可用性.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g 中的分区....
oracle11g驱动包
oracle11g安装步骤.oracle11g安装步骤.oracle11g安装步骤.oracle11g安装步骤.
Oracle 11g是一款甲骨文公司成功打造的功能强大的关系数据库管理系统,目前共发行了linux版本和windows版本,Oracle数据库的高效性、安全性、稳定性、延展性,是其成功的关键因素,世界上的几乎所有大型信息化系统都...
oracle 11g p10350787_111070升级补丁oracle 11g p10350787_111070升级补丁
Oracle11g日常操作与维护手册\Oracle11g日常操作与维护手册\Oracle11g日常操作与维护手册\
oracle11g自动建库oracle11g自动建库oracle11g自动建库oracle11g自动建库oracle11g自动建库
Oracle 11g 官方中文文档 包括: Oracle 11g:ORACLE ACTIVE DATA GUARD.pdf Oracle Database 11g 高可用性.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g 中的分区.pdf Oracle 数据库...
Linux版本 Oracle11g数据库 64位 完整安装包
Windows环境使用exp,imp,expdp,impdp,sqldur2等导入导出数据命令的时候,需要安装Oracle11g客户端环境
龙蜥anolis系统oracle11g安装包,内含安装脚本支持龙蜥系统下自动安装数据库,并还原数据库dmp文件
ojdbc678,oracle11g驱动
在centos下安装oracle11g 使用yum源进行搭建 包括配置监听
oracle 11g gateway
《Oracle11g从入门到精通》面向数据库管理人员和数据库开发人员,从实际角度出发,系统地介绍了数据库和Oracle的相关概念和原理、Oracle的数据库管理(如安装与启动,用户权限、备份与恢复等),以及Oracle的应用...
oracle10G和11G的OCI.dll,主要用于navicat工具;oracle10G和11G的OCI.dll,主要用于navicat工具
Oracle11g在windows服务器部署记录文档,以及遇到的问题和解决方法