哪个更有效的 Oracle SQL？并行创建表作为选择还是并行插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8719161/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Which is more efficent Oracle SQL? PARALLEL CREATE TABLE AS SELECT or PARALLEL INSERT?
提问by phileas fogg
I need to back up tables and their data as part of software testing. The exact tables and the data they contain may vary so I can't hard code the DDL or the data. The back up tables will have similar names as the original tables but with the prefix "QA_". Similar, but not identical (except for the prefix), only because in order to work around the 31 character limit for table names I have to abbreviate some of the names.
作为软件测试的一部分，我需要备份表及其数据。确切的表和它们包含的数据可能会有所不同，因此我无法对 DDL 或数据进行硬编码。备份表将具有与原始表相似的名称，但带有前缀“QA_”。相似但不完全相同（前缀除外），只是因为为了解决表名的 31 个字符限制，我必须缩写一些名称。
Which would be the faster way of doing this? Using a cursor object and looping through the tables to get their DDL like this:
哪种方法更快？使用游标对象并循环遍历表以获取它们的 DDL，如下所示：
select dbms_metadata.get_ddl(''TABLE'',' || '''' || cursor_rec.object_name || '''' || ') from dual
Using that DDL to create the backup tables and then populating those tables with:
使用该 DDL 创建备份表，然后使用以下内容填充这些表：
INSERT /*+ parallel(' || new_table_name || 'DEFAULT) */ INTO ' || new_table_name || ' SELECT * FROM ' || table_name || ''
Or doing a simple:
或者做一个简单的：
CREATE TABLE' || new_table_name 'PARALLEL AS SELECT * from ' || table_name || ''
Which is the faster of the two methods?
这两种方法哪个更快？
回答by Mark J. Bobak
Well, both INSERT /*+ PARALLEL */and CREATE TABLE .... PARALLEL AS SELECT ...are going to do direct load insert. So, they are largely going to use the same code path, for the data loading.
同时，双方INSERT /*+ PARALLEL */并CREATE TABLE .... PARALLEL AS SELECT ...会做直接加载插入。因此，他们将在很大程度上使用相同的代码路径来加载数据。
CTAS will need to first do the DDL to create the table, before proceeding with the load. But, that's a small amount of fixed work. The larger the table being copied, the smaller factor that will be.
CTAS 将需要首先执行 DDL 以创建表，然后再继续加载。但是，这是少量的固定工作。被复制的表越大，系数越小。
Finally, I'll just mention, that you should look into NOLOGGINGto further improve performance.
最后，我只想提一下，您应该考虑NOLOGGING进一步提高性能。
On the CTAS, you can just add the NOLOGGINGkeyword after the PARALLELkeyword. For the INSERT /*+ PARALLEL */, you'll need to first do an ALTER TABLE ... NOLOGGINGto enable it.
在 CTAS 上，您可以在NOLOGGING关键字后添加PARALLEL关键字。对于INSERT /*+ PARALLEL */，您需要先执行 anALTER TABLE ... NOLOGGING以启用它。
Note that there are a few things you need to know, if you decide to go with NOLOGGING. First, onlydirect-load will do nologging. For tables, that means CTAS and INSERT with PARALLEL or APPEND hint.
请注意，如果您决定使用NOLOGGING. 首先，只有直接加载才会不记录。对于表，这意味着 CTAS 和 INSERT 与 PARALLEL 或 APPEND 提示。
Secondly, consider the recovery implications of NOLOGGING. When a table is loaded with NOLOGGING option, the data is NOTlogged to REDO. So, you won't be able to recover the data in the table, unless you use a backup taken afterthe data was sucessfully loaded and committed.
其次，考虑 的恢复影响NOLOGGING。当使用 NOLOGGING 选项加载表时，数据不会记录到 REDO。因此，您将无法恢复表中的数据，除非您使用成功加载和提交数据后的备份。
Hope that helps.
希望有帮助。
回答by Jon Heller
Create-table-aswill generally be faster because it does not include many options, such as "primary keys, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column default values".
Create-table-as通常会更快，因为它不包含很多选项，例如“主键、唯一键、外键、检查约束、分区标准、索引和列默认值”。
DBMS_METADATA by default will include most of those objects, which can significantly increase the time required to copy data. (Although you may want those objects to exist for selecting, validation, etc.)
默认情况下，DBMS_METADATA 将包括大多数这些对象，这会显着增加复制数据所需的时间。（尽管您可能希望这些对象存在以进行选择、验证等）
