为什么使用CREATE TABLE AS SELECT比使用INSERT WITH SELECT更快？

Question

我使用INNER JOIN进行了查询，结果有1200万行。我想把它放在一个表格里。我做了一些测试，发现使用AS SELECT子句创建表格比先创建表格再运行INSERT WITH SELECT更快。我不明白为什么。谁能为我解释一下？谢谢。

- Fernando Delago

---

多快？ - Gordon Linoff

现有的表上有没有任何索引或触发器？ - Alan

你先执行了 INSERT ... SELECT，再执行了 CREATE ... SELECT 吗？也许正确的页面仍然在系统缓存中。 - sticky bit

INNER JOIN 中使用的表有一个字段索引。我在 where 子句中使用它。 但是，我的问题是为什么相同的查询在使用 WITH 子句创建表时比使用 INSERT INTO TABLE SELECT blablabla 更快。 - Fernando Delago

如果您尝试其他查询，是否也有很大的差异？在这两种情况下创建了多少个范围？ - miracle173

Answer 1

如果您使用“create table as select”（CTAS）

CREATE TABLE new_table AS 
    SELECT * 
    FROM old_table

当你执行直接路径插入时，数据将自动插入。如果你执行

INSERT INTO new_table AS 
    SELECT * 
    FROM old_table

你执行了一个传统的插入操作。如果你想执行直接路径插入，你需要使用APPEND提示符。因此，你需要执行

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

要实现与“CREATE TABLE AS SELECT”相似的性能。

通常传统插入操作是如何工作的？

Oracle检查表的空闲列表，查找仍有空闲空间的表段已使用块。如果该块不在缓存中，则将其读入缓存中。最终，该块被写回磁盘。 在此过程中，为该块编写撤消日志（只需要少量数据），更新数据结构，例如必要时更新位于段标题中的空闲列表，并将所有这些更改也写入重做缓冲区。

直接路径插入操作是如何工作的？

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

如何提高CTAS和直接路径插入操作的性能？

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

• 您可以并行执行选择操作

• 您可以并行进行插入操作

• 如果在插入操作期间必须维护索引、约束甚至触发器，则可能会严重影响插入操作的速度。因此，您应该避免这种情况，并在插入后创建索引，可能使用novalidate选项创建约束。

- miracle173

Answer 2

使用SELECT STATEMENT创建的表没有主键、索引、自增列... 列允许为空。这个表不需要写入事务日志（因此也不会回滚）。看起来像是一个“裸表”。

使用INSERT ... SELECT时，必须先创建表，以便定义主键、索引、自增列等。这样做会使用事务日志，而在处理大量数据时，速度非常慢。

- user6313281

---

谢谢！现在我明白了为什么使用SELECT子句的INSERT语句很慢。只是提供信息，我的工作是删除并插入1200万行数据到表中，需要运行4个小时。现在是时候愉快地编写更好的代码了！再次感谢。 - Fernando Delago