收集索引的统计数据或删除创建？ [英] Gather stats on an Index or drop create?
问题描述
删除和重新创建索引与使用dbms.gather_index_stats具有相同的效果吗？ （它与重建/更新索引具有相同的效果）
Does dropping and recreating an index have the same effect as to using dbms.gather_index_stats? (Does it have the same effect as rebuilding/updating the index)
或者这两个完全不同的东西是不应该相互比较的？
Or are these two completely different things that should not be compared to one another?
推荐答案
不同之处在于，收集统计信息会刷新有关当前索引的元数据，而删除和重新创建索引则是，删除和重新创建指数。
The difference is, gathering statistics refreshes the metadata about the current index whereas dropping and re-creating the index is, er, dropping and re-creating the index.
也许很容易理解与一个有效例子的区别。所以让我们创建一个表和一个索引：
Perhaps it is easy to understand the difference with a worked example. So let's create a table and an index:
SQL> create table t23
2 as select object_id as id, object_name as name from user_objects
3 /
Table created.
SQL> create index i23 on t23(id)
2 /
Index created.
SQL> select o.object_id, i.last_analyzed, i.distinct_keys
2 from user_objects o
3 join user_indexes i
4 on (i.index_name = o.object_name)
5 where o.object_type = 'INDEX'
6 and i.index_name = 'I23'
7 /
OBJECT_ID CREATED LAST_ANALYZED DISTINCT_KEYS
---------- -------------------- -------------------- -------------
116353 23-NOV-2013 00:15:39 23-NOV-2013 00:15:39 167
1 row selected.
SQL>
由于11g Oracle在创建索引时会自动收集统计信息。因此索引创建和最后分析显示相同的日期时间。在以前的版本中，我们必须在创建索引后显式收集统计信息。 了解更多信息。
Since 11g Oracle automatically gathers statistics when we create an index. So index creation and last analysis show the same datetime. In previous versions we had to explicitly gather statistics after we created the index. Find out more.
接下来，我们将添加一些数据并刷新统计数据：
Next, we'll add some data and refresh the statistics:
SQL> insert into t23 values (9999, 'TEST1')
2 /
1 row created.
SQL> insert into t23 values (-8888, 'TEST 2')
2 /
1 row created.
SQL> exec dbms_stats.gather_index_stats(user, 'I23')
PL/SQL procedure successfully completed.
SQL> select o.object_id, i.last_analyzed, i.distinct_keys
2 from user_objects o
3 join user_indexes i
4 on (i.index_name = o.object_name)
5 where o.object_type = 'INDEX'
6 and i.index_name = 'I23'
7 /
OBJECT_ID CREATED LAST_ANALYZED DISTINCT_KEYS
---------- -------------------- -------------------- -------------
116353 23-NOV-2013 00:15:39 23-NOV-2013 00:26:28 169
1 row selected.
SQL>
现在，与统计相关的元数据已更改，但索引是相同的数据库对象。然而，如果我们删除并重新创建索引，我们将得到一个新的数据库对象：
Now the metadata relating to statistics has changed but the index is the same database object. Whereas if we drop and re-create the index we get a new database object:
SQL> drop index i23
2 /
Index dropped.
SQL> create index i23 on t23(id)
2 /
Index created.
SQL> select o.object_id, i.last_analyzed, i.distinct_keys
2 from user_objects o
3 join user_indexes i
4 on (i.index_name = o.object_name)
5 where o.object_type = 'INDEX'
6 and i.index_name = 'I23'
7 /
OBJECT_ID CREATED LAST_ANALYZED DISTINCT_KEYS
---------- -------------------- -------------------- -------------
116354 23-NOV-2013 00:27:50 23-NOV-2013 00:27:50 169
1 row selected.
SQL>
在正常运营中，我们几乎不需要放弃重新创建一个索引。这种技术在加载大量数据时非常适合，并且在非常罕见的索引损坏情况下。由于性能原因（据称它重新平衡偏斜的索引），互联网仍然建议定期重建索引的站点，但这些站点没有产生证明长期效益的基准，当然也从未包括时间和重建过程浪费了CPU周期。
In normal operations we hardly ever need to drop and re-create an index. It is a technique which is sometime appropriate when loading very large amounts of data and in very rare instances of index corruption. The interwebs still throw up sites which recommend regular rebuilding of indexes for performance reasons (allegedly it "re-balances" skewed indexes) but these sites don't produce the benchmarks to prove the long-term benefits, and certainly never include the time and CPU cycles wasted by the re-building exercise.
我目前正在尝试处理加载优化和更新
大量的数据和思考哪个更好做
"I'm currently trying to handle optimisation of loading and updating huge amount of data and pondered on which was better to do"
重建索引比刷新统计数据需要更多的工作。显然是正确的，因为重建包括将统计数据作为子任务收集。问题是，与丢弃索引并重新创建后续索引相比，对具有索引的表进行批量DML是否更有效。可以更快地将数据加载到没有索引的表中，然后重新创建它们。
Rebuilding an index requires more work than refreshing the stats. Obviously true, because rebuilding includes gathering stats as a sub-task. The question is whether it is more efficient to undertake bulk DML against a table with its indexes in place compared to dropping the indexes and re-creating the afterwards. It can be quicker to load data into a table without indexes and re-create them afterwards.
这里没有严格的规则：它取决于你拥有多少索引，受影响的行与表的整个大小的比例，是否您需要索引来强制执行关系完整性约束，等等。操作之间也存在很大差异：您可能希望删除批量插入的索引，但保留它们以进行更新，具体取决于WHERE子句需要哪些索引以及更新是否会影响索引列。
There is no hard-and-fast rule here: it depends on how many indexes you have, the proportion of the rows affected against the whole size of the table , whether you need the indexes to enforce relational integrity constraints, and so on. There is also a big difference between operations: you might want to drop indexes for bulk inserts but retain them for updates, depending on what indexes you need for your WHERE clause and whether the update affects indexed columns.
简而言之，您需要对自己的特定方案进行基准测试。在性能问题上，这通常就是答案。
In short, you need to benchmark your own specific scenario. This is often the answer when it comes to performance questions.
这篇关于收集索引的统计数据或删除创建？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
