Oracle 创建index时 自动收集index 统计信息 但partition index要特别注意
Index drop 后重建可以自动收集index 统计信息
但如果 unusable index，如果这个index是partition 的，在rebuild index partition时 有个重大的问题，只是gather 了索引 partition上的statistics，没有gather 索引的 global statistics，导致在enable constraint使用global index statistics 出现问题。
由于Foreign Key上 global statistics为0 ，所以estimated的cost是1，而实际是xM行，loop过多了。
EXCLUDE=STATISTICS Or EXCLUDE=INDEX_STATISTICS During Datapump Import Still Analyzes The Indexes (Doc ID 793585.1)
Symptoms
You are using Data Pump import (impdp) using the following parameters:
EXCLUDE=STATISTICS
- OR -
EXCLUDE=INDEX_STATISTICS
EXCLUDE=TABLE_STATISTICS
Tables are not being analyzed in both cases, however, it is still analyzing the indexes.
The Datapump import statements:
impdp scott/******
Directory=<DIR_NAME>
Dumpfile=<DUMP_NAME>.dmp
Logfile=<LOG_NAME>.log
EXCLUDE=STATISTICS ---> excluded both table and index stats
-- OR --
impdp scott/******
Directory=<DIR_NAME>
dumpfile=<DUMP_NAME>.dmp
logfile=<LOG_NAME>.log
EXCLUDE=INDEX_STATISTICS -->excluded table stats
EXCLUDE=TABLE_STATISTICS -->excluded index stats
Changes
Cause
Oracle, by default, collects statistics for an index during index creation. It is done by design.
The internal parameter "_optimizer_compute_index_stats", is set to TRUE by default.
Solution
This parameter can be set to FALSE to avoid the index statistics during import.
EXAMPLE:
SQL> alter system set "_optimizer_compute_index_stats"=FALSE;
- OR -
Set the parameter in the pfile/spfile
_optimizer_compute_index_stats=FALSE