ORACLE CTAS(create table as select)使用注意点
看到这篇文章Bewareof default values when using CTAS,关于create table asselect
(CTAS)值得注意的地方:使用这条sql创建的表不会带默认值。
操作以下实验证明之:
INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales SELECT product_id, customer_id, TRUNC(sales_date), 3, promotion_id, quantity, amount FROM sales_activity_direct;
scott@TICKET>; create table p
2 ( id number primary key ,
3 username varchar(25) ,
4 passwd varchar(24),
5 email varchar(30),
6 birth date,
7 tel number ,
8 sex char(1));
表已创建。
scott@TICKET>; alter table p add agenumber ;
表已更改。
创建一个唯一约束
scott@TICKET>; alter table p modify tel unique;
表已更改。
创建一个检查约束
scott@TICKET>; alter table p addconstraint p_ck_age check(age>;0 and age<;150);
表已更改。
创建一个默认约束
scott@TICKET>; alter table p modify sex default '0';
表已更改。
scott@TICKET>; desc user_constraints;
名称 是否为空? 类型
------------------------------------------------- ----------------------------
OWNER NOT NULLVARCHAR2(30)
CONSTRAINT_NAME NOT NULLVARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULLVARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
查看原表中的各种约束信息.
scott@TICKET>; select constraint_name,constraint_type,status fromuser_constraints where table_name='P';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
SYS_C0015996 P ENABLED
SYS_C0015997 U ENABLED
P_CK_AGE C ENABLED
通过查询创建相关的表信息.
scott@TICKET>; create table persionas select * from p;
表已创建。
scott@TICKET>; select * from persion;
未选定行
scott@TICKET>; insert into persion select * from p;
已创建0行。
检查新表中的约束信息
scott@TICKET>; select constraint_name,constraint_type,status fromuser_constraints where table_name='PERSION';
未选定行
再次证明了使用CTAS存在一些问题.
Oracle查询了一下文档(11gr1),关于 ASsubquery 里面叙述到
Oracle Database automatically defineson columns in the new table any NOTNULL constraints that were explicitly
created on the corresponding columns of the selected table if the subqueryselects the column rather than an expression
containing the column. If any rowsviolate the constraint, then the database does not create the table and returns
an error.
显示的NOT NULL约束自动会带到新表。
NOT NULL constraints that were implicitly created byOracle Database on columns of the selected table (for example,
for primary keys) are not carried over to the new table.
隐式的NOT NULL约束不会带到新表,如主键。
In addition, primary keys, uniquekeys, foreign keys, check constraints, partitioning criteria, indexes, andcolumn
default values are not carried over to thenew table.
另外,主键,唯一,外键,check约束,分区,索引以及列的默认值不会带到新表。
If the selected table is partitioned,then you can choose whether the new table will be partitioned the sameway,
partitioned differently, or notpartitioned. Partitioning is not carried over to the new table. Specify anydesired
partitioning as part of the CREATE TABLE statement before the ASsubquery clause.
在新表上可以选择是否像像旧表那样分区,或者不同的分区形式,或者创建非分区表。在AS subquery句之前指定。
来自:豆丁网