EXCLUDE=STATISTICS Or EXCLUDE=INDEX_STATISTICS During Datapump Import Still Analyzes The Indexes
APPLIES TO:
Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
SYMPTOMS:
You are using Data Pump import (impdp) using the following parameters:
EXCLUDE=STATISTICS
– OR –
EXCLUDE=INDEX_STATISTICS
EXCLUDE=TABLE_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=DUMP_DIR
Dumpfile=Exp.dmp
Logfile=Exp.log
EXCLUDE=STATISTICS —> excluded both table and index stats
— OR —
impdp scott/******
Directory=DUMP_DIR
dumpfile=Exp.dmp
logfile=Exp.log
EXCLUDE=INDEX_STATISTICS –>excluded table stats
EXCLUDE=TABLE_STATISTICS –>excluded index stats
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
Recent Posts
