VALIDATE STRUCTURE Online

[Nikee]

Hello,

When I ran the following index validate command it is not populating the table INDEX_STATS.

ANALYZE INDEX "ADR2~I01" VALIDATE STRUCTURE Online;

What is the reason behind it and how can I will get to know the values for height, blocks, lf_rows, lf_blks and del_lf_rows ???

Thanks,
Nikee

[jmodic]

Use it without the ONLINE option:

ANALYZE INDEX "ADR2~I01" VALIDATE STRUCTURE;

As per oracle documentation, when you perform "ANALYZE... VALIDATE STRUCTURE ONLINE", no statistics are gathered, the process only validates the integrity of the object:

When you validate the structure of an object ONLINE, Oracle does not collect any statistics, as it does when you validate the structure of the object OFFLINE

.

Regarding the question about where to get various index statistics from: You can get all of them (except del_lf_rows) in DBA_/ALL_USER_INDEXES. You must of course first collect those statistics either with ANALYZE command or (preferebly) by using DBMS_STATS.