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