Virtual Column
Oracle Database 11g lets you include a virtual column in a table. Unlike normal columns, a virtual column’s values aren’t inserted directly into a table. The virtual column you specify is always based on computing an expression or a function based on one or more other columns in the same table. Once you create a virtual column, you can query it just as you do any other column.
Virtual columns have the following important features:
- You can index a virtual column.
- You can use a virtual column in all types of DDL and DML statements.
- The database doesn’t store the values of the virtual column on disk because these values are only computed on-the-fly when you reference the virtual column.
- The datatype for a virtual column is optional. If you don’t explicitly specify the datatype, the virtual column will inherit the same datatype as the underlying expression.
- You can collect optimizer statistics on a virtual column.
- You can partition a table or an index on a virtual column.
You can create a virtual column either when you create a table, or later on, by using the alter table statement. There are two ways to create a virtual column. The first method, shown here, is to create the virtual column when you create the table:
SQL> create table admin_emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
photo BLOB,
SAL number(7,2),
hrly_rate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
comm NUMBER(7,2),
deptno NUMBER(3) NOT NULL
);
The column HRLY_RATE is a virtual column. You must specify the generated always as clause when you create a virtual column. Actually, the generated always part of this clause is optional. The generated always clause tells us that the database doesn’t store the column values on disk, but rather, generates them only when a SQL statement refers to this virtual column. The last part of the clause (as) shows the expression the database uses to compute the values for the virtual column. In this example, the values of the HRLY_RATE column are generated from the SAL column, by computing the expression sal/2080. Because the SAL column provides the annual salary, the expression sal/2080 gives you the hourly salary for an employee.
The following restrictions apply to the creation of a virtual column:
- You create a virtual column only on a heap table, which is the normal Oracle table. You can’t create a virtual column on an index-organized, temporary, external, object, or cluster table.
- A virtual column can’t refer to another virtual column.
- A virtual column can be built only on the columns from the same table as the virtual column is in.
- The output of the virtual column must always be a scalar value.
- The virtual column can’t be an Oracle-supplied datatype or a user-defined type, LOB, or LONG RAW type.
Note that you can’t directly update a virtual column. That is, the following statement would fail if we assume that HRLY_RATE is a virtual column:
SQL> update table employees
set hrly_rate ...
You can, however, specify a virtual column in the where clause of an update statement. Similarly, you can specify a virtual column in the where clause of a delete statement.
The second way to create a virtual column is to do so after table creation, by using the alter table statement, as shown here:
SQL> alter table employees add (income AS (salary + (salary*commission_pct)));
Virtual Column-Based Partitioning
Once you create a table with one or more virtual columns, you can then employ the new virtual column-based partitioning scheme to partition that table.
SQL> CREATE TABLE users (
id NUMBER,
username VARCHAR2(20),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(username), 1, 1))
) VIRTUAL
)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
PARTITION part_v_z VALUES ('V','W','X','Y','Z')
)
enable row movement;
The following code inserts two rows into each partition defined in the table.
SQL> INSERT INTO users (id, username) VALUES (1, 'Andy Pandy');
SQL> INSERT INTO users (id, username) VALUES (1, 'Burty Basset');
SQL> INSERT INTO users (id, username) VALUES (1, 'Harry Hill');
SQL> INSERT INTO users (id, username) VALUES (1, 'Iggy Pop');
SQL> INSERT INTO users (id, username) VALUES (1, 'Oliver Hardy');
SQL> INSERT INTO users (id, username) VALUES (1, 'Peter Pervis');
SQL> INSERT INTO users (id, username) VALUES (1, 'Veruca Salt');
SQL> INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote');
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');
The following query shows the data was distributed as expected.
SQL> COLUMN table_name FORMAT A25
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN high_value FORMAT A40
SQL> SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
USERS PART_A_G 'A', 'B', 'C', 'D', 'E', 'F', 'G' 2
USERS PART_H_N 'H', 'I', 'J', 'K', 'L', 'M', 'N' 2
USERS PART_O_U 'O', 'P', 'Q', 'R', 'S', 'T', 'U' 2
USERS PART_V_Z 'V', 'W', 'X', 'Y', 'Z' 2
4 rows selected.
The last line of the code in the example shows that you can specify row movement when partitioning on a virtual column. When you enable row movement, if the virtual column’s value belongs to another partition, a row migrates to the appropriate partition from its current partition.
Note:if you don't enable row movement, when you update the partition key column, you'll get ORA-14402 error
参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
http://www.oracle-base.com/articles/11g/partitioning-enhancements-11gr1.php
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
- **新特性**:Oracle 11g引入了参考分区(reference partitioning)、区间分区(interval partitioning)和虚拟列分区(virtual column partitioning),以及增强了子分区(subpartitioning)功能。 - **好处**:这些新特性...
在这个特定的场景中,我们关注的是如何配置 NHibernate 与 Oracle 11g 数据库进行交互,包括对表的读、写、查询以及分页操作。Oracle 11g 是 Oracle 公司的一个企业级数据库管理系统,广泛应用于各种规模的企业。 ...
Oracle 19C是Oracle数据库的一个重要版本,OCP(Oracle Certified Professional)是Oracle公司针对其产品提供的专业认证,旨在证明个人在Oracle数据库管理、优化和运维方面的技能和知识。以下是对Oracle 19C OCP相关...
在本案例中,我们关注的是如何使用NHibernate 3.2版本连接Oracle数据库,并实现自动生成数据库结构。 首先,我们需要了解NHibernate的工作原理。ORM框架的主要目标是消除传统的SQL编程,通过提供一种面向对象的方式...
**2.8 virtual columns in 11g** ##### 2.8.1 creating a virtual column - **定义**: 创建基于其他列计算结果的新列。 - **语法**: SQL DDL语句示例。 - **应用场景**: 动态计算和存储复杂表达式的值。 ##### ...
- Oracle 11g中的分区表新特性: 包括Interval Partitioning、System Partitioning以及Virtual Column-Based Partitioning等。 4. Oracle锁和阻塞 在并发环境中,锁是Oracle用来控制数据访问的机制。 - 锁的概念: ...
NHibernate 是一个流行的开源对象关系映射(ORM)框架,它允许开发人员使用.NET Framework 或 .NET Core 在各种数据库系统上工作,包括Oracle。Oracle 是一个功能强大的关系型数据库管理系统,广泛应用于大型企业级...
Oracle Database 12c是Oracle公司推出的一个重要版本,它带来了许多创新特性和改进,旨在提升数据库性能、可扩展性和管理效率。以下是其中32个关键新特性的概述,结合实际案例进行分析: 1. **多租户架构...
Oracle数据库是世界上最广泛使用的数据库管理系统之一,尤其在企业级应用中占据重要地位。"Oracle培训18天老师笔记"很可能是对Oracle SQL和PL/SQL基础的深入学习资料,涵盖了数据库查询、数据管理以及程序设计等多个...
3. **列级加密**(Column-Level Encryption):Oracle数据库支持透明数据加密(Transparent Data Encryption, TDE),允许对特定列进行加密,使得只有持有正确密钥的应用程序或用户才能解密并访问数据。 4. **审计...
通过使用Oracle的Instead of触发器,我们可以对含有virtual column的视图进行修改、插入操作,并将所有数据加密保存到数据库中。在Oracle中,我们可以使用触发器来实现数据加密解密处理。触发器可以对数据进行实时的...
Oracle 11g 引入了多项新的分区特性,进一步增强了分区的功能和灵活性: 1. **区间分区**(Interval Partitioning):自动创建分区,特别适合于处理无法预测的数据增长情况。 2. **分区顾问**(Partition Advisor)...
首先,为了使用NHibernate与Oracle数据库进行连接,你需要安装相关的NuGet包,包括NHibernate本身以及Oracle的驱动,如`NHibernate`和`Oracle.ManagedDataAccess`。这些库将提供必要的API来建立数据库连接和执行SQL...
在数据库 ORACLE 还维护了一组虚表(virtual table),记录当前数据库的活动,这些表称为动态性能表。动态性能表不是真正的表,许多用户不能直接存取,DBA 可查询这些表,可以建立视图,给其它用户授予存取视图权。 ...
<property name="Email" column="UserEmail" /> ``` **3. SessionFactory和Session** SessionFactory是Nhibernate的核心组件,负责创建Session实例,每个Session代表一次数据库会话。在应用程序启动时,创建...
protected internal virtual void RetrieveBySql(string sql); public void ShadowCopyTo(object targetEntity); public void ShadowCopyTo(T targetEntity); protected static double Sum(string columnName); ...
DDL for indexes on virtual columns would specify the column expression instead of the column name Fixed Session > Set Main Connection menu icon transparency issue Using the Search Next function in a ...
protected internal virtual void RetrieveBySql(string sql); public void ShadowCopyTo(object targetEntity); public void ShadowCopyTo(T targetEntity); protected static double Sum(string columnName); ...
protected internal virtual void RetrieveBySql(string sql); public void ShadowCopyTo(object targetEntity); public void ShadowCopyTo(T targetEntity); protected static double Sum(string columnName); ...