oracle 在空表上创建索引后插入数据还是在oracle上插入数据后创建唯一索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1983979/
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
Insertion of data after creating index on empty table or creating unique index after inserting data on oracle?
提问by DKSRathore
Which option is better and faster? Insertion of data after creating index on empty table or creating unique index after inserting data. I have around 10M rows to insert. Which option would be better so that I could have least downtime.
哪个选项更好更快？在空表上创建索引后插入数据或在插入数据后创建唯一索引。我有大约 10M 行要插入。哪个选项会更好，以便我可以减少停机时间。
回答by womp
Insert your data first, then create your index.
先插入数据，然后创建索引。
Every time you do an UPDATE, INSERT or DELETE operation, any indexes on the table have to be updated as well. So if you create the index first, and then insert 10M rows, the index will have to be updated 10M times as well (unless you're doing bulk operations).
每次执行 UPDATE、INSERT 或 DELETE 操作时，表上的任何索引也必须更新。因此，如果您先创建索引，然后插入 10M 行，则索引也必须更新 10M 次（除非您正在执行批量操作）。
回答by RC.
It is faster and better to insert the records and then create the index after rows have been imported. It's faster because you don't have the overhead of index maintenance as the rows are inserted and it is better from a fragmentation standpoint on your indexes.
插入记录然后在导入行后创建索引更快更好。它更快，因为在插入行时您没有索引维护的开销，并且从索引的碎片角度来看更好。
Obviously for a unique index, be sure that the data you are importing is unique so you don't have failures when trying to create the index.
显然，对于唯一索引，请确保您导入的数据是唯一的，这样在尝试创建索引时就不会出现故障。
回答by Bob Jarvis - Reinstate Monica
As others have said, insert first and add the index later. If the table already exists and you have to insert a pile of data like this, drop all indexes and constraints, insert the data, then re-apply first your indexes and then your constraints. You'll certainly want to do intermediate commits to help preclude the possibility that you'll run out of rollback segment space or something similar. If you're inserting this much data it might prove useful to look at using SQL*Loader to save yourself time and aggravation.
正如其他人所说，先插入，然后再添加索引。如果该表已经存在并且您必须像这样插入一堆数据，请删除所有索引和约束，插入数据，然后首先重新应用您的索引，然后重新应用您的约束。您当然希望进行中间提交以帮助排除回滚段空间或类似空间用完的可能性。如果您要插入这么多数据，那么考虑使用 SQL*Loader 来节省时间和减轻压力可能会很有用。
I hope this helps.
我希望这有帮助。
