create table进阶学习(一)

create table进阶学习

---11g,可指定列类型为rowid
SQL> create table t_test(a int,b rowid);

Table created.

SQL> desc t_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------

 A                                                  NUMBER(38)
 B                                                  ROWID

SQL> create table t_object as select object_id from dba_objects where rownum<=2;

Table created.

SQL> insert into t_test(a,b) select object_id,rowid from t_object;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from t_test;

         A B
---------- ------------------
        20 AAAQVEAAKAAAMyjAAA
        46 AAAQVEAAKAAAMyjAAB
       
注:可指定oracle rowid为列类型,但官方不保证其合理性

---alter table可同时添加多个列
SQL> create table t_test(a int);

Table created.

SQL> alter table t_test add (b int,c int);

Table altered.
  
  

---oracle11g新特性,virutal column
SQL> create table t_source(a int,b int);

Table created.

SQL> insert into t_source select 1,2 from dual;

1 row created.

SQL> commit;

Commit complete.    

 

SQL> create table t_virtual(c) as (select a+b from t_source);

Table created.

---虚拟列表可以单独插入非表达式的值到虚拟列表
SQL> insert into t_virtual select a from t_source;

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t_virtual select a+b from t_source;

1 row created.

SQL> commit;

Commit complete.

--佐证 虚拟列表可以单独插入非表达式的值到虚拟列表
SQL> select * from t_virtual;

         C
----------
         3
         1
         3

 

---删除重建表
SQL> drop table t_virtual purge;

Table dropped.

SQL> create table t_virtual(c) as (select a+b from t_source);

Table created.

--虚拟列表一量创建即有值,引自源表
SQL> select * from t_virtual;

         C
----------
         3  
        

---虚拟列表可建索引
SQL> create index idx_t_virtual on t_virtual(c);

Index created.

--官方手册说虚拟列不占用磁盘,好像情况不是如此
SQL> select segment_name,segment_type,blocks from user_segments where segment_na
me='T_VIRTUAL';

SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE                             BLOCKS
------------------------------------ ----------
T_VIRTUAL
TABLE                                         8    

---再次向虚拟列引用源表插入数据
SQL> insert into t_source select level,level+3 from dual connect by level

2999 rows created.

SQL> commit;

Commit complete.

--虚拟列表依旧是1行记录
SQL> select count(c) from t_virtual;

  COUNT(C)
----------
         1  
        
SQL> exec dbms_stats.gather_table_stats(user,'t_virtual',cascade=>true);

PL/SQL procedure successfully completed.

----收集统计信息,依旧是8个block
SQL> select segment_name,blocks from user_segments where segment_name='T_VIRTUAL
';

SEGMENT_NAME
--------------------------------------------------------------------------------

    BLOCKS
----------
T_VIRTUAL
         8
        

----这里有些晕了,上述试验与官方不符,据官方所言,虚拟列表的数据源于源表,而现源表有>1条记录,但虚拟列表依旧是1条记录
The virtual_column_definition clause lets you create a virtual column. A virtual column is not stored on disk. Rather,
the database derives the values in a virtual column on demand by computing a set of expressions or functions.
Virtual columns can be used in queries,--虚拟列表用于查询,ddl,dml,也可以建索引,收集统计信息
 DML, and DDL statements. They can be indexed, and you can collect statistics on them.
 Thus, they can be treated much as other columns    
 
 
---虚拟列表的操作限制
Restrictions on Virtual Columns

---仅适用于关系堆表,即我们最常用的表,iot,external,object,temporary不适用
You can create virtual columns only in relational heap tables. Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
 
---as 子句表达式的相关限制
The column_expression in the AS clause has the following restrictions:

----表达式不能引用另一个虚拟列
It cannot refer to another virtual column by name.
 
---表达式引用的列同源于一个表
Any columns referenced in column_expression must be defined on the same table.
 
---如引用函数,此函数运行结果须确定不变,且不能用虚拟列作为分区表的分区键列
It can refer to a deterministic user-defined function, but if it does, then you cannot use the virtual column as a partitioning key column.
 
--表达式的值必须是标量值
The output of column_expression must be a scalar value.
 

---虚拟列类型不能是oracle自定义类型或lob等
The virtual column cannot be an Oracle supplied data type, a user-defined type, or LOB or LONG RAW.
 
---表达式中不用引用对一个虚拟列的调用,即不能在表达式中使用虚拟列
You cannot specify a call to a PL/SQL function in the defining expression for a virtual column that you want to use as a partitioning column.

--oracle11g虚拟列的资料,也可以阅读:
http://www.ningoo.net/html/2007/oracle11g_new_feature_virtual_column.html

--继续测试虚拟列
SQL> create table t_virtual(a int,b int,c as (a+b) virtual);

Table created.

SQL> insert into t_virtual(a,b,c) values(1,2,3);
insert into t_virtual(a,b,c) values(1,2,3)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SQL> insert into t_virtual(a,b) values(1,3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_virtual;

         A          B          C
---------- ---------- ----------
         1          3          4

SQL>

---如下选项generated always及virtual可不指定    
SQL> create table t_virtual(a int,b int,c int generated always as (a+b) virtual)
;

Table created.

SQL> insert into t_virtual(a,b) select level,level+3 from dual connect by lev
3e1;

29 rows created.

SQL> commit;

Commit complete.

---虚拟列c自动计算得么值
SQL> select * from t_virtual;

         A          B          C
---------- ---------- ----------
         1          4          5
         2          5          7
         3          6          9
         4          7         11
         5          8         13
         6          9         15
         7         10         17
         8         11         19
         9         12         21
        10         13         23
        11         14         25

         A          B          C
---------- ---------- ----------
        12         15         27
        13         16         29
        14         17         31
        15         18         33
        16         19         35
        17         20         37
        18         21         39
        19         22         41
        20         23         43
        21         24         45
        22         25         47

         A          B          C
---------- ---------- ----------
        23         26         49
        24         27         51
        25         28         53
        26         29         55
        27         30         57
        28         31         59
        29         32         61

29 rows selected.
    

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-752332/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-752332/