Oracle中巧用CTAS快速建立表格

CTAS是通过查询，然后根据查询的结果来建立表格的一种方式。显然通过这种方式可以省去定义表结构的方法，而且在创建表的同时导入数据。不过在使用这个方法的时候，仍然有些内容需要引起数据库管理员的注意。（SQL Server数据库中成批导入数据的几个常用方法）

　　

 

　　如上图，就是笔者利用CTAS建立表格的过程。其中AD_USER是原表，而AD_USER_CTAS是利用CTAS方法建立的表。利用DESC查看两个表的结构，大家可以发现起不是简单的表复制而已。

　　一、可以修改列名和字段的数据类型。

　　大部分时候，在创建表（SQL 2008中控制要求重新创建表的更改）时数据库管理员可能不希望照搬照抄原由的表格，而总希望能够更改某些内容。如列的名字或者数据类型等等。在使用CTAS方法来创建表的时候，是可以做到在一定范围之内对原有的内容进行更改。不过需要注意的是，笔者这里说的是在一定范围之内可以对原有的内容进行更改，而不是全部内容。即其可以更改的内容是有限的。

　　一般来说，在利用CTAS方法创建表格时，可以更改目标列的列名。如上图所示，可以直接在Select查询语句中使用别名，来重定义目标列的名字。然后再利用CTAS来创建表格的时候，就会以目标列的别名来命名新的字段名称。另外需要注意的是，虽然可以修改数据类型，但是其修改需要受到严格的限制。如上图所示，在SELECT查询语句中，可以利用数据类型转换函数对目标数据类型进行转换，然后在新建立的表格中，就会以目标列的数据类型来定义新列的数据类型。也就是说，如果需要更改某个列的数据类型的话，则必须在SELECT查询语句中通过数据类型的转换函数来实现。而不能够通过其他方法来调整数据类型。除非是表格建立好之后，再在建立好的表格中进行更改。

　　二、约束条件不一定会继承。

　　利用CTAS建立表格时，原有的约束条件是否会被继承下来呢?再有些参考书上说，约束条件是不会被保存的。不过根据笔者对ORACLE数据库的了解，觉得这个说法太过于绝对。其实并不是所有的约束条件都不会被继承。恰恰相反，一般情况下都会被新表所继承，而只有在一些特殊的情况下才不会被保留。

　　如上图所示，在原表中有一个字段叫做VALUE。在创建这个表的时候笔者给其加了一个“非空”的约束条件。利用CTAS创建新表之后，大家可以看到新表中也有一个字段VALUE，而且这个字段的约束条件也是非空的。可见，利用CTAS创建新表的时候，原表中的非空约束还是保存下来了。不过这个继承是有条件的，只有在不对原表中的数据类型进行更改的情况下，才能够保留这个约束条件。再如上图所示，笔者在SELECT语句中对NUM数据类型进行了转换，从数值型数据转换为字符型数据。在这个过程中，新表的数据类型确实转换过来了，但是从结果中可以看到，这个字段的约束条件没有继承过来。原表中这个字段有一个非空的约束条件，而在目的表中则没有。

　　根据笔者的了解，只有在更改数据类型的情况下，这个非空等约束条件才不会被保留下来。如果只是更改了目标列的名字(通过查询列的别名来实现)，如上图所示的name列，这个约束条件仍然会保留下来。在某些参考书上，对这个约束条件的描述有错误。笔者以前在学习的时候，也受其误导过。各位数据库管理员在这方面需要引起重视。为了加深理解，各位数据库管理员可以回去进行测试，以判断笔者所说的是否准确。

　　三、索引与默认值不一定会被继承。

　　虽然是否为空等约束条件可以保留下来，但是并不是说目标列中的所有内容都可以被继承下来。在定义表的时候，除了指定某个列是否为非空之外，还可以设置其他的一些内容。如可以设置某个列的默认值，或者对某个列设置唯一索引等等。那么这些字段的定义在使用CTAS来创建表格的时候是否会被保存下来呢?

　　如上图所示，在原表中NUM字段设置了一个默认值为0。然后利用CTAS创建表之后，好象这个默认值不存在了。这是否说明这个默认值不会被继承了呢?其实，上面这个例子中之所以这个默认值没有在新表中的字段中被保留下来，主要是因为其更改了数据类型，将数值型改为了字符型的数据类型。如果不更改数据类型的话，这个默认值的设置仍然会像非空条件设置那样被保留下来。即使列名更改了，默认值仍然会被继承。结合上面的非空条件设置，我们可以得出一个结论，当数据类型被更改了，则针对列的非空条件与默认值的设置，都不会被保留下来。但是如果只是目标列的名字该了，则非空条件与默认值的相关设置会被保留。这一点大家在执行这个操作的时候，需要特别的留意，否则的话就可能会出现问题。

　　虽然默认值在不更改数据类型的情况下，会被保存下来。但是需要注意的是，索引无论在什么情况下都不会被保留(根据笔者的测试)。如在上例中，笔者在原表的VALUE字段中设置了一个唯一索引。此时即使不更改字段VALUE字段的数据类型或者目标列的名字，其在新表中也不会保留下来。所以，索引跟默认值、非空条件是不一样的。至少在CTAS方案中他们体现出了不一样的特性。

　　四、可以只复制表结构，而不插入记录。

　　在利用CTAS来创建新表的时候，出了可以创建表结构之外，还可以将相关的记录也插入到目的表中。但是有时候数据库管理员可能不需要插入记录，而只需要一个表结构即可。其实要实现这个需求也是很简单的。如上例中，即使原表中没有任何记录，只要这些字段存在，其仍然可以创建新表。这可以给我们什么启示呢?其实如果SELECT语句没有返回任何记录的话，那么CTAS语句就不会往新表中插入记录。在执行CTAS语句时可以利用WHERE条件语句来过滤记录。如只插入部分数据等等。在设置这个WHERE条件语句时，如果没有一条记录符合这个条件，那么系统就只会返回表的框架结构。此时就可以复制表的结构，而不插入任何记录了。

　　五、提高执行效率，最好不做日志记录。

　　当新表中的记录比较多而没有采用WHERE条件语句进行过滤(或者过滤后仍然有很多的记录)，则在执行这个语句时可能会花费比较长的时间。因为在默认情况下，往数据库表中插入数据的时候，不仅需要更新记录(数据高速缓存或者数据文件)，而且还需要更新相关的日志(重做日志高速缓存或者重做日志文件)。为此当需要插入大量记录的时候，其速度就会比较慢，如会频繁的触发日志切换事件或者会触发DBWR进程等等。为此在往数据库中插入大量数据的时候，最好在语句中使用NOLOGGING选项，让数据库不记录相关的重做日志信息，以提高插入的速度。另外为了保障数据的安全，最好在插入完成之后，马上对数据库或者相关的表空间进行备份。以为此时由于相关更新没有记录到重做日志中，为此当出现故障时将很难恢复。

　　六、利用排序来优化存储结构。

　　一般情况下，表中的记录都是按照时间的先后来进行存储的，而不是按照关键子或者索引的顺序。为此当记录多了的时候，这个查询的速度就会比较慢。所以在向数据库表中插入数据的时候，有时候最好对记录进行重新排序，以优化记录的存储结构。所以在执行CTAS语句的时候，如果在创建表的过程中同时插入大量的记录，最好先对记录进行排序。然后再将其插入到目标表中。为了数据库的性能考虑，笔者强烈建议这么做。

     七、重新定义新表的表空间，初始化参数，增加新列，合并表空间和连锁记录等

      这可能也可以解释为什么索引会失效的原因吧。