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虛擬列的資料,也可以閱讀:

--繼續測試虛擬列
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/，如需轉載，請註明出處，否則將追究法律責任。

create table進階學習(一)

相關文章