自学内容网 自学内容网

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


原文地址:https://blog.csdn.net/jnrjian/article/details/145097863

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!