ORACLE 在索引表上插入性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3414221/
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
ORACLE Insert performance on indexed tables
提问by mehmet6parmak
Why Insert statements perform slower on a indexed table?
为什么插入语句在索引表上执行速度较慢？
回答by Patrick
This is actually the same kind of question as:
这实际上是同一种问题：
Why does it take more time to put all my groceries in the correct place in my kitchen than leaving everything in the bags after I visited my groceries store?
为什么在我逛完杂货店后，将所有杂货放在厨房的正确位置比把所有东西都放在袋子里需要更多的时间？
This is because when storing your groceries, you want them on a nice, well known position so that it is easier to find them afterwards.
这是因为在存放您的杂货时，您希望它们放在一个好的、众所周知的位置，以便以后更容易找到它们。
A database has to do the same.
数据库也必须这样做。
- If you have a table without index, it can just add new data at the end of the table.
- If you have an index, the database has to perform more work. It will probably still put the record at the end of the table, but additionally it will update its index to make sure that if you want to find that record afterwards, it will find it more quickly than without index.
- 如果您有一个没有索引的表，它可以在表的末尾添加新数据。
- 如果您有索引，则数据库必须执行更多工作。它可能仍会将记录放在表的末尾，但另外它会更新其索引以确保如果您想在之后找到该记录，它会比没有索引更快地找到它。
This also means that adding more indexes will further slow down inserts.
这也意味着添加更多索引将进一步减慢插入速度。
It should be clear that you only want to create an index if you will also use it afterwards. If you only create an index and you are not using it afterwards to improve the performance of a query, there's no need to have the index as it will only slow down the inserts, and not improve any query.
应该清楚的是，如果您以后还要使用它，您只想创建一个索引。如果您只创建一个索引并且之后没有使用它来提高查询的性能，则没有必要拥有该索引，因为它只会减慢插入速度，而不会改善任何查询。
回答by a'r
An INSERTstatement has to both add the data to the table data blocks and update any indexes that the table has defined.
一个INSERT语句既将数据添加到表中的数据块，并更新该表定义任何索引。
Clearly, if you have an index, the insert will need to do some more 'work' to update the index as well.
很明显，如果你有一个索引，插入将需要做一些更多的“工作”来更新索引。
回答by Rajesh Chamarthi
Indexes allow you to store more information (in index blocks) about the data which helps retrieving the data easier. Obviously, you are doing additional work initially to benefit later (for selects).
索引允许您存储有关数据的更多信息（在索引块中），这有助于更轻松地检索数据。显然，您最初正在做额外的工作以在以后受益（对于选择）。
回答by Steve
If you need to delete some data at a later stage from the table you inserted into would it not be a good idea to have an index on the table. The extra time taken to insert the data into the table can be compensated by the deletes running faster on an indexed table (if the indexed columns are used in the delete WHERE clause. ????
如果稍后需要从插入的表中删除一些数据，那么在表上建立索引不是一个好主意。将数据插入表所花费的额外时间可以通过在索引表上运行更快的删除来补偿（如果在删除 WHERE 子句中使用了索引列。????
