`

Oracle 11g Virtual Column(原创)

 
阅读更多

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

0
0
分享到:
评论

相关推荐

    Oracle11g:面向_DBA_和开发人员的重要新特性

    - **新特性**:Oracle 11g引入了参考分区(reference partitioning)、区间分区(interval partitioning)和虚拟列分区(virtual column partitioning),以及增强了子分区(subpartitioning)功能。 - **好处**:这些新特性...

    nhibernate配置连接oracle11实现表的读,写,查,分页

    在这个特定的场景中,我们关注的是如何配置 NHibernate 与 Oracle 11g 数据库进行交互,包括对表的读、写、查询以及分页操作。Oracle 11g 是 Oracle 公司的一个企业级数据库管理系统,广泛应用于各种规模的企业。 ...

    Oracle 19C OCP课堂笔记.zip

    Oracle 19C是Oracle数据库的一个重要版本,OCP(Oracle Certified Professional)是Oracle公司针对其产品提供的专业认证,旨在证明个人在Oracle数据库管理、优化和运维方面的技能和知识。以下是对Oracle 19C OCP相关...

    NHibernate3.2 连接oracle自动生成数据库

    在本案例中,我们关注的是如何使用NHibernate 3.2版本连接Oracle数据库,并实现自动生成数据库结构。 首先,我们需要了解NHibernate的工作原理。ORM框架的主要目标是消除传统的SQL编程,通过提供一种面向对象的方式...

    Oracle运维最佳实践-上.pdf 带书签

    **2.8 virtual columns in 11g** ##### 2.8.1 creating a virtual column - **定义**: 创建基于其他列计算结果的新列。 - **语法**: SQL DDL语句示例。 - **应用场景**: 动态计算和存储复杂表达式的值。 ##### ...

    TianleSoftware Oracle中文学习手册

    - Oracle 11g中的分区表新特性: 包括Interval Partitioning、System Partitioning以及Virtual Column-Based Partitioning等。 4. Oracle锁和阻塞 在并发环境中,锁是Oracle用来控制数据访问的机制。 - 锁的概念: ...

    NHibernate+Oracle搭建项目架构示范代码

    NHibernate 是一个流行的开源对象关系映射(ORM)框架,它允许开发人员使用.NET Framework 或 .NET Core 在各种数据库系统上工作,包括Oracle。Oracle 是一个功能强大的关系型数据库管理系统,广泛应用于大型企业级...

    Oracle Database 12c 数据库32个新特性与案例总结

    Oracle Database 12c是Oracle公司推出的一个重要版本,它带来了许多创新特性和改进,旨在提升数据库性能、可扩展性和管理效率。以下是其中32个关键新特性的概述,结合实际案例进行分析: 1. **多租户架构...

    oracle培训18天老师笔记

    Oracle数据库是世界上最广泛使用的数据库管理系统之一,尤其在企业级应用中占据重要地位。"Oracle培训18天老师笔记"很可能是对Oracle SQL和PL/SQL基础的深入学习资料,涵盖了数据库查询、数据管理以及程序设计等多个...

    oracle database value

    3. **列级加密**(Column-Level Encryption):Oracle数据库支持透明数据加密(Transparent Data Encryption, TDE),允许对特定列进行加密,使得只有持有正确密钥的应用程序或用户才能解密并访问数据。 4. **审计...

    ORACLE数据加密解密处理的设计及实现.pdf

    通过使用Oracle的Instead of触发器,我们可以对含有virtual column的视图进行修改、插入操作,并将所有数据加密保存到数据库中。在Oracle中,我们可以使用触发器来实现数据加密解密处理。触发器可以对数据进行实时的...

    Oracle分区技术

    Oracle 11g 引入了多项新的分区特性,进一步增强了分区的功能和灵活性: 1. **区间分区**(Interval Partitioning):自动创建分区,特别适合于处理无法预测的数据增长情况。 2. **分区顾问**(Partition Advisor)...

    初步用NHibernate连接Oracle数据库实现数据的增\删\改\查等功能

    首先,为了使用NHibernate与Oracle数据库进行连接,你需要安装相关的NuGet包,包括NHibernate本身以及Oracle的驱动,如`NHibernate`和`Oracle.ManagedDataAccess`。这些库将提供必要的API来建立数据库连接和执行SQL...

    ORACLE数据字典与视图[参照].pdf

    在数据库 ORACLE 还维护了一组虚表(virtual table),记录当前数据库的活动,这些表称为动态性能表。动态性能表不是真正的表,许多用户不能直接存取,DBA 可查询这些表,可以建立视图,给其它用户授予存取视图权。 ...

    Nhibernate应用实例

    <property name="Email" column="UserEmail" /> ``` **3. SessionFactory和Session** SessionFactory是Nhibernate的核心组件,负责创建Session实例,每个Session代表一次数据库会话。在应用程序启动时,创建...

    ORM及代码生成器C#源码(2012最新版Kenly.DBFramework4.6.5.5)

    protected internal virtual void RetrieveBySql(string sql); public void ShadowCopyTo(object targetEntity); public void ShadowCopyTo(T targetEntity); protected static double Sum(string columnName); ...

    PLSQL.Developer v11.0.4.1774 主程序+ v11中文包+keygen

    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 ...

    ORM及代码生成器C#源码(最新版V4.5.8.5、非常适于ASP.NET MVC)

    protected internal virtual void RetrieveBySql(string sql); public void ShadowCopyTo(object targetEntity); public void ShadowCopyTo(T targetEntity); protected static double Sum(string columnName); ...

    ORM及代码生成器和插件C#源码(DBFrameworkV4.5.3)

    protected internal virtual void RetrieveBySql(string sql); public void ShadowCopyTo(object targetEntity); public void ShadowCopyTo(T targetEntity); protected static double Sum(string columnName); ...

Global site tag (gtag.js) - Google Analytics