为什么CREATE TABLE AS SELECT比SELECT INSERT更快 [英] Why CREATE TABLE AS SELECT is more faster than INSERT with SELECT

问题描述

我使用INNER JOIN进行查询，结果为1200万行. 我喜欢把它放在桌子上. 我做了一些测试，当我使用AS SELECT子句创建表时，比先创建表并在运行SELECT之后运行INSERT更快. 我不明白为什么. 有人可以为我解释吗? Tks

I make a query with INNER JOIN and the result was 12 millions lines. I like to put this in a table. I did some tests and when I created the table using clause AS SELECT was more faster than, create the table first and run a INSERT with SELECT after. I don't understand why. Somebody can explain for me? Tks

推荐答案

如果使用创建表作为选择"(CTAS)

If you use 'create table as select' (CTAS)

CREATE TABLE new_table AS 
    SELECT * 
    FROM old_table

您会自动执行

you automatically do a direct-path insert of the data. If you do an

INSERT INTO new_table AS 
    SELECT * 
    FROM old_table

您进行常规插入.如果要执行直接路径插入，则必须使用APPEND提示.所以你必须要做

you do a conventional insert. You have to use the APPEND-hint, if you want to do a direct path insert instead. So you have to do

INSERT /*+ APPEND */ INTO new_table AS 
    SELECT * 
    FROM old_table

获得与"CREATE TABLE AS SELECT"中类似的性能.

to get a similar performance as in 'CREATE TABLE AS SELECT'.

常规的常规插入件如何工作?

Oracle检查 表的空闲列表 ，该表段的已使用块仍具有可用空间.如果该块不在缓冲区高速缓存中，则将其读入缓冲区高速缓存.最终，该块被读回到磁盘. 在此过程中，写入该块的撤消操作(此处仅需要少量数据)，例如，更新数据结构.必要时，段头中包含的空闲列表，所有这些更改也将写入重做缓冲区.

Oracle checks the free list of the table for an already used block of the table segment that has still free space. If the block isn't in the buffer cache it is read into the buffer cache. Eventually this block is read back to the disk. During this process undo for the block is written (only a small amount of data is necessary here), data structures are updated, e.g. if necessary, the free list,that esides in the segment header and all these changes are written to the redo-buffer, too.

直接路径插入如何工作?

该过程在表的高水位标记上方分配空间，即超出已使用的空间.它将数据直接写入磁盘，而无需使用缓冲区高速缓存.并且还将其写入重做缓冲区.提交会话后，高水位标记将超出新的书面数据，并且其他会话现在可以看到此数据.

The process allocates space above the high water mark of the table, that is, beyond the already used space. It writes the data directly to the disk, without using a buffer cache. And it is also written to the redo buffer. When the session is committed, the highwater mark is raised beyond the new written data and this data is now visible to other sessions.

如何改善CTAS和直接路径插入?

• 您可以在NOLOGGING模式下创建故事，而不写入任何重做信息.如果这样做，则应在插入后对包含该表的表空间进行备份，否则，如果需要，将无法恢复该表.

• 您可以并行进行选择

• You can create he tale in NOLOGGING mode, than no redo information is written. If you do this, you should make a backup of the tablespace that contains the table after the insert, otherwisse you can not recover the table if you need this.

• You can do the select in parallel

您可以并行插入

如果在插入操作期间必须维护索引和约束甚至触发器，则这可能会大大降低插入操作的速度.因此，您应该避免这种情况，并在插入之后创建索引，并可能使用novalidata创建约束.

If you have to maintain indexes and constraints or even triggers during an insert operation this can slow down your insert operation drastically. So you should avoid this and create indexes after the insert and maybe create constraints with novalidata.

这篇关于为什么CREATE TABLE AS SELECT比SELECT INSERT更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！