`
longgangbai
  • 浏览: 7339391 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

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

 
阅读更多
ORACLE CTAS(create table as select)使用注意点
     看到这篇文章Beware of default values when using CTAS,关于create table as select 
(CTAS)值得注意的地方:使用这条sql创建的表不会带默认值。
操作以下实验证明之:


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  age number  ;

表已更改。

创建一个唯一约束
scott@TICKET> alter table p modify tel unique;

表已更改。

创建一个检查约束
scott@TICKET> alter table p  add constraint 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 NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(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 from user_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  persion as 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 from user_constraints where table_name='PERSION';

未选定行
  再次证明了使用CTAS存在一些问题.


Oracle查询了一下文档(11gr1),关于 AS subquery 里面叙述到
	Oracle Database automatically defines on columns in the new table any  NOT NULL  constraints that were explicitly 
created on the corresponding columns of the selected table if the subquery selects the column rather than an expression
 containing the column. If any rows violate the constraint, then the database does not create the table and returns 
 an error.
显示的NOT NULL约束自动会带到新表。

	NOT NULL  constraints that were implicitly created by Oracle 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, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column
 default values are not carried over to the new table.

另外,主键,唯一,外键,check约束,分区,索引以及列的默认值不会带到新表。

	If the selected table is partitioned, then you can choose whether the new table will be partitioned the same way,
	partitioned differently, or not partitioned. Partitioning is not carried over to the new table. Specify any desired
	partitioning as part of the  CREATE TABLE  statement before the  AS subquery  clause.

在新表上可以选择是否像像旧表那样分区,或者不同的分区形式,或者创建非分区表。在AS subquery句之前指定。
 




 

分享到:
评论

相关推荐

    oracle备份语句

    常见的逻辑备份方法包括使用`CREATE TABLE AS SELECT`(CTAS)语句创建新表来保存数据,以及使用Oracle的导出(Export)工具进行备份。 #### 二、`CREATE TABLE AS SELECT`语句详解 `CREATE TABLE AS SELECT`...

    Oracle复制表数据的两种用法

    第二种方法是使用Oracle的`CREATE TABLE AS SELECT`(CTAS)语句,这一步骤创建一个新的表并立即填充来自查询的结果。基本语法如下: ```sql CREATE TABLE table2 AS SELECT column1, column2, ... FROM table1 ...

    Oracle 大数据量操作优化.pdf

    6. **并行执行**:利用并行提示(PARALLEL HINT)可以充分利用多CPU资源,提高DML操作的效率,尤其是对于大表的CTAS(CREATE TABLE AS SELECT)和INSERT操作。 7. **Direct-Path插入**:使用`/*+APPEND*/`提示进行...

    一个Oracle建表SQL语句的优化.pdf

    Oracle数据库提供了Create table as select (CTAS)语句来完成这一操作。CTAS语句是Oracle中一个非常有用的命令,它将SELECT查询的结果集直接插入到一个新创建的表中。该语句不仅创建表,还同时填充数据,可以极大地...

    Oracle 大数据量操作优化

    - 使用`CREATE TABLE AS SELECT` (CTAS)语句可以快速创建并填充新表。 - 示例命令: ``` create table t1 nologging parallel (degree 2) as select * from t; ``` 4. **Direct-Path插入** - Direct-Path...

    第二部分 ORACLE 数据对象的分析1(表_约束_视图)

    通过CTAS(Create Table As Select)语句可以快速创建一个表的备份,不包含原表的约束、索引等,仅复制数据: ```sql CREATE TABLE tb_name_bak AS SELECT * FROM tb_name; ``` ### 约束(Constraint) 除了主键...

    ocp复习资料

    在提供的题目中,我们讨论了 `CREATE TABLE AS SELECT` (CTAS) 语句,这是 Oracle SQL 中用于快速创建新表并填充数据的一种方法。CTAS 命令允许用户基于一个查询结果来创建新的表结构。题目中给出的 CTAS 语句如下:...

    基于Oracle Text电子政务全文检索技术的应用.pdf

    在实现电子政务全文检索系统时,首先需要创建和维护文本索引,这可以通过Oracle的CTAS(CREATE TABLE AS SELECT)语句或者CTX_DDL包来完成。接着,可以使用Oracle Text的CONTAINS函数或配对的索引查询语法来执行查询...

    Oracle 10gR2压缩(Compress)技术

    - CTAS(CREATE TABLE AS SELECT)数据压缩:创建新表时就应用压缩。 - INSERT /*+ APPEND */压缩:用于插入大量数据,适用于批量加载操作。 - ALTER TABLE ... MOVE COMPRESSION:移动表到新的表空间并启用压缩。 -...

    Oracle SQL优化实例讲解.pdf

    文档提到了使用CTAS(Create Table As Select)语句结合dba_objects系统表来创建一个大型的测试表。dba_objects是Oracle中的一个系统表,包含了数据库对象的信息。通过CTAS语句,可以高效地复制数据以构建一个用于...

    专家调优秘密之改善Oracle数据库性能

    通过CTAS(CREATE TABLE AS SELECT)操作,可以重新组织表中的数据行,使其与主键索引顺序一致,从而减少磁盘读取时的等待时间。这种优化策略适用于OLTP环境,特别是通过主键索引进行连续查询的场景。然而,对于随机...

    Oracle专家调优秘密

    - **Row Re-Sequencing**:通过创建表时使用`CTAS`(Create Table As Select)语句,或在Oracle 9i及以上版本中使用分区表和分区索引来优化I/O,尤其是针对大数据量的表,可以显著提高查询速度。 - **数据组织**:...

    052题库-汇总整理-csdn.pdf

    此外,文件还讨论了如何将数据从一个表插入到另一个表,以及如何使用不同方法合并数据,这些方法包括使用MERGE命令和CTAS(CREATE TABLE AS SELECT)。这些是Oracle中合并数据常用的技术,MERGE命令特别适用于基于...

    Oracle从入门到精通

    - **CTAS(子查询建表)**:使用`CREATE TABLE AS SELECT`语句创建新表并填充数据。 - **截取**:删除表中的所有数据但保留表结构。 - `TRUNCATE TABLE table_name;` - **给表加注释**:使用`COMMENT`命令。 - `...

    DBA 应遵循的 Oracle 调优原则

    - **调整查询策略**:例如,使用`CTAS`(Create Table As Select)代替常规的插入操作,以优化数据写入模式并减少排序所需的I/O操作。 - **优化数据分布**:合理分配数据块,确保数据在磁盘上的物理布局尽可能与查询...

    oracle从入门到精通.pdf

    ### Oracle从入门到精通知识点概览 #### 一、SQL基础 **1.1 基本概念** - **Oracle数据库简介**:Oracle是一种广泛使用的多模型数据库管理系统,以其高性能、高可靠性和强大的功能著称。 - **数据库系统**:包括...

    Oracle设计规范.docx

    - 在执行DML操作前,使用CTAS(Create Table As Select)语句备份数据。 **5.4 INSERT时需写全列名** - 明确指定插入的所有列名,防止因后续修改导致的问题。 **5.5 大数据量的DML** - 对于大数据量的操作,应...

    oracle\Oracle从入门到精通

    - 使用`CREATE TABLE AS SELECT` (CTAS)快速创建新表。 - 表的注释和约束条件用于增强数据完整性和安全性。 - **2.5 视图(VIEW)** - 视图是基于SQL查询的虚拟表,可以简化复杂的查询并提高数据安全性。 - **2.6...

    Oracle调优心得

    - **解决方案**:可以通过优化查询或使用Create Table As Select (CTAS)等方法来避免不必要的行重新排序,减少I/O开销。 #### SQL语句的调优 - **SQL语句优化**:SQL语句的编写方式直接影响其执行效率。通过改进...

Global site tag (gtag.js) - Google Analytics