create table进阶学习
---11g,可指定列类型为rowid
SQL> create table t_test(a int,b rowid);
SQL> create table t_test(a int,b rowid);
Table created.
SQL> desc t_test;
Name Null? Type
----------------------------------------- -------- ----------
Name Null? Type
----------------------------------------- -------- ----------
A NUMBER(38)
B ROWID
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为列类型,但官方不保证其合理性
---------- ------------------
20 AAAQVEAAKAAAMyjAAA
46 AAAQVEAAKAAAMyjAAB
注:可指定oracle rowid为列类型,但官方不保证其合理性
---alter table可同时添加多个列
SQL> create table t_test(a int);
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);
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;
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;
SQL> select * from t_virtual;
C
----------
3
1
3
----------
3
1
3
---删除重建表
SQL> drop table t_virtual purge;
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;
SQL> select * from t_virtual;
C
----------
3
----------
3
---虚拟列表可建索引
SQL> create index idx_t_virtual on t_virtual(c);
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';
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
------------------------------------ ----------
T_VIRTUAL
TABLE 8
---再次向虚拟列引用源表插入数据
SQL> insert into t_source select level,level+3 from dual connect by level
----表达式不能引用另一个虚拟列
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.
SQL> insert into t_virtual(a,b) values(1,3);
SQL> insert into t_virtual(a,b) select level,level+3 from dual connect by lev
3e1;
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;
SQL> select count(c) from t_virtual;
COUNT(C)
----------
1
SQL> exec dbms_stats.gather_table_stats(user,'t_virtual',cascade=>true);
----------
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
';
SQL> select segment_name,blocks from user_segments where segment_name='T_VIRTUAL
';
SEGMENT_NAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
BLOCKS
----------
T_VIRTUAL
8
----------
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
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:
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.
--继续测试虚拟列
SQL> create table t_virtual(a int,b int,c as (a+b) virtual);
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
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
---------- ---------- ----------
1 3 4
SQL>
---如下选项generated always及virtual可不指定
SQL> create table t_virtual(a int,b int,c int generated always as (a+b) 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;
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
---------- ---------- ----------
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
---------- ---------- ----------
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
---------- ---------- ----------
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/