sql - 为什么 CREATE TABLE AS SELECT 比 INSERT with SELECT 更快

Question

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

Answer 1

如果您使用“创建表作为选择”(CTAS)

CREATE TABLE new_table AS 
    SELECT * 
    FROM old_table

你会自动做一个 direct-path insert 的数据。如果你做一个

INSERT INTO new_table AS 
    SELECT * 
    FROM old_table

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

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

获得与“CREATE TABLE AS SELECT”类似的性能。

通常的常规 Blade 如何工作？

Oracle 检查 free list 表段的一个已使用块的表中仍有可用空间。如果块不在缓冲区缓存中，则将其读入缓冲区缓存。最终这个块被读回磁盘。
在这个过程中，块的撤销被写入(这里只需要少量数据)，数据结构被更新，例如如有必要，段头中的空闲列表和所有这些更改也会写入重做缓冲区。

直接路径插入如何工作？

进程在 上方分配空间高水位线 表，即超出已经使用的空间。它将数据直接写入磁盘，而不使用缓冲区缓存。并且它也被写入重做缓冲区。当 session 被提交时，高水位标记会超过新写入的数据，并且该数据现在对其他 session 可见。

如何改进 CTAS 和直接路径插入？

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

您可以并行执行选择

可以并行插入

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