ORACLE CTAS(create table as select)使用注意点

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, amountFROM 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句之前指定。
 


来自：豆丁网