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
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
  • December 24, 2018 | 35 views
  • Comments