在数据加载之前还是之后创建 Oracle SQL 索引更好？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2160752/
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
Is it better to create Oracle SQL indexes before or after data loading?
提问by AticusFinch
I need to populate a table with a huge amount of data (many hours loading) on an Oracle database, and i was wondering which would be faster, to create an index on the table before loading it or after loading it. I initially thought that inserting on an indexed table is penalized, but then if i create the index with the full table, it will take a lot of time. Which is best?
我需要在 Oracle 数据库上填充包含大量数据（加载数小时）的表，我想知道哪个会更快，在加载之前或加载后在表上创建索引。我最初认为在索引表上插入会受到惩罚，但是如果我使用完整表创建索引，则会花费很多时间。哪个最好？
回答by Thilo
Creating indexes after loading the data is much faster. If you load data into a table with indexes, the loading will be very slow because of the constant index updates. If you create the index later, it can be efficiently populated just once (which may of course take some time, but the grand total should be smaller).
加载数据后创建索引要快得多。如果将数据加载到带有索引的表中，由于索引不断更新，加载将非常缓慢。如果您稍后创建索引，它可以有效地仅填充一次（这当然可能需要一些时间，但总计应该更小）。
Similar logic applies to constraints. Also enable those later (unless you expect data to fail the constraints and want to know that early on).
类似的逻辑适用于约束。也可以稍后启用（除非您希望数据无法满足约束并希望尽早知道）。
回答by Erich Kitzmueller
The only reason why you might want to create the index first is to enforce unique constraints. Otherwise, loading is much faster with a naked table - no indexes, no constraints, no triggers enabled.
您可能希望首先创建索引的唯一原因是强制执行唯一约束。否则，使用裸表加载要快得多 - 没有索引、没有约束、没有启用触发器。
回答by bkm
Creating an index after the data load is the recommended practice for bulk loads. You must be sure about the incoming data quality though especially if you are using unique indices. The absence of the index means that data validation that occurs due to the presence of unique indexes will not happen. Another issue for you to consider is whether you have a one time load operation or is it going to be a regular affair? If it is a regular affair, then you can drop the indexes before each data load and recreate them after a successful load.
数据加载后创建索引是批量加载的推荐做法。您必须确保传入的数据质量，尤其是在您使用唯一索引时。没有索引意味着不会发生由于存在唯一索引而发生的数据验证。您需要考虑的另一个问题是您是否有一次性加载操作，还是将成为常规事件？如果这是常规事件，那么您可以在每次数据加载之前删除索引，并在成功加载后重新创建它们。
