Virtual columns of data-make the C field in the database equal to A+b

Directly with Update aaaaa AA set aa.f = (AA.A+AA.B) on the line

=======================================

The new knowledge--Virtual columns

The advantage of a virtual column is that if c=a+b inserts the data, the value is only inserted into the a=1,b=2; then C is automatically 3.

Oracle temporary table and virtual column(2013-07-31 15:06:55) reproduced

Tags:Oracle Learning Temp Table Virtual column

Category: Oracle Basic Learning

Tables are the most basic structure for storing user data in a database. Here I briefly learned about temporary tables and virtual columns.

Tables can be divided into temporary tables and permanent tables according to permanence.

temporary tables are created using the Create global temporary table keyword, and the temporary table created is visible only to the current session, and the other sessions are invisible; When the temporary table is created, the data about the table is stored in the data dictionary (Temporary table space is also permanent, but the data inside is not, after exiting the session only the knowledge does truncate TABLE operation, so the temporary table space is to manually drop when not used), but no space allocated to the temporary table, It is not allocated until data is inserted. The operation of the temporary table is the same as for the DM and Ddll operations on the Permanent table (normal table), and they are only different from the life cycle. Temporary tables split session temporary table and transaction temporary table, session temporary table when created will specify on commit preserve rows,session temporary table is automatically deleted at the end of the session, only the session is visible. The transaction temp table must be created with an on commit delete rows (if none is specified, the schema is a transaction temp table), the transaction temp table survives only in the created transaction, and the end of the transaction disappears.

Some restrictions on creating temporary tables:

1) cannot be partitioned, cannot be clustered, cannot be indexed by organization

2) cannot have foreign key constraints in temporary tables

3) Temporary tables cannot contain columns from nested tables

4) Temporary table does not support lob_storage_clause: TABLESPACE , storage_clause , or logging_clause .

5) Parallel Update,delete,merge not supported

6) The only segment property that can be specified when creating a temporary table is a tablespace, and the tablespace can only be a temporary tablespace if the temporary object that 0ra-02195 attempts to create is reported in a temporary table space in the other tablespace

7) Distributed transactions do not support temporary tables

If you use the AS subquery statement to create a table, you can specify no data type, or you can set the column of the foreign key without specifying the data type

Virtual Columns : Virtual columns are not saved in the disk, and are obtained only at the time of acquisition, based on the columns stored on disk. A virtual column can create an index that collects statistical information. The syntax for creating a virtual column is: Column datatype (generatedalways) as (exp) virtrual, When you define a virtual column without specifying a data type, the data type of the value returned in the Virtual column expression is used by default, and the value of the virtual column cannot be updated with the update and insert operation, and its value is obtained based on the corresponding column value calculation for the new join or update. Indexes created on virtual columns are equivalent to creating a function index

Limitations of Virtual columns:

1) Virtual columns can only be used in the relation heap, and are not available in the Organization Index table, the external table, the Object table (objct table), the clustered report, or the staging table.

2) The expression of a virtual column cannot point to another virtual column (the virtual column is evaluated at query time based on an expression, so there is a post-press), so there is uncertainty; a virtual column expression involves a column that is only in the same table; a virtual column expression can use a user-defined function. However, if a user-defined function is used, the virtual column cannot be partitioned; The value returned by the virtual column expression must be a definite value;

3) The data type of the virtual column cannot be a user-defined type, or LOB or LONG RAW .

Virtual column-related test scripts and results:

sql> Create or Replace function test1
2 AS
3 begin
4 return 23;
5 end;
6/

The function has been created.

Sql> CREATE TABLE Testvirtual (age number,
2 V_test generated always as (AGE+10),
3 V_test1 generated always astest1);
V_test1 generated always as Test1)
*
An error occurred on line 3rd:
ORA-02000: Missing (keyword

sql> INSERT into testvirtual values (10,20);
INSERT into testvirtual values (10,20)
*
An error occurred on line 1th:
ORA-54013: Do not allow INSERT operations on virtual columns

sql> INSERT into testvirtual values (10);
INSERT into testvirtual values (10)
*
An error occurred on line 1th:
ORA-00947: Not Enough value

sql> INSERT into testvirtual (age) VALUES (10);

1 rows have been created.
Sql> select * from Testvirtual;

Age V_test
---------- ----------
10 20

Sql> ALTER TABLE testvirtual Add (v_test1 as (v_test+1) virtual);
ALTER TABLE testvirtual Add (v_test1 as (v_test+1) virtual)
*
An error occurred on line 1th:
ORA-54012: A virtual column is referenced in a column expression
Sql> ALTER TABLE testvirtual Add (v_test1 as (age+1) virtual);

The table has changed.

sql> Create or Replace function Testxu (p_innumber) return number deterministic
As
2 begin
3 return p_in+20;
4 End;
5/

The function has been created.

Sql> CREATE TABLE Testpartition (age number,
2 V_age as (TESTXU (age)) virtual)
3 partition Byrange (V_age) (
4 partition P1 values LessThan (30),
5 partition P2 values LessThan (100),
6 partition P3 values LessThan (200),
7 PARTITIONPN values less than (MAXVALUE)
8);
V_age as (Testxu (age)) virtual)
*
An error occurred on line 2nd:
ORA-54021: You cannot use a PL/SQL expression in a partitioned column or a sub-partition column

Sql> CREATE TABLE Testpartition (Agenumber,
2 V_age as (age+10) virtual)
3 partition Byrange (V_age) (
4 partition P1 values LessThan (30),
5 partition P2 values LessThan (100),
6 partition P3 values LessThan (200),
7 PARTITIONPN values less than (MAXVALUE)
8);

The table is created.

Network Literature Reference:

Virtual Column http://yangtingkun.itpub.net/post/468/409211

Virtual columns of data-make the C field in the database equal to A+b