Analyze Index Validate Structure Online Does Note Populate INDEX_STATS (Doc ID 283974.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 27-Jan-2012***


Symptoms

A sql command 'analyze index validate structure online' does not populate index_stats or index_histogram while without the online clause it does populate the views.

Testcase


SQL> create table test (id number, text varchar2(9));
SQL> create index test_indx on test(id);
SQL> insert into test values (1, 'A');
SQL> insert into test values (2, 'B');
SQL> insert into test values (3, 'C');
SQL> commit;


SQL> analyze index test_indx validate structure online;

Index analyzed.

SQL> select * from index_stats;

no rows selected

SQL> select * from index_histogram;

no rows selected



SQL> analyze index test_indx validate structure ;

Index analyzed.

SQL> select * from index_stats;

HEIGHT         BLOCKS NAME
---------- ---------- ------------------------------
PARTITION_NAME                 LF_ROWS    LF_BLKS    LF_ROWS_LEN LF_BLK_LEN
------------------------------ ---------- ---------- ----------- ----------
   BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ---------- ----------- ---------- ----------- ---------------
DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS PRE_ROWS   PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------------- ---------- ------------ -------------- ----------------
1                   8 TEST_INDX
                                        3          1          42       7996
         0          0           0          0           0               0
            3                 1        7996         42          1            1
                   2          0            0              0                0


SQL> select * from index_histogram;

REPEAT_COUNT KEYS_WITH_REPEAT_COUNT
------------ ----------------------
           0                      0
           1                      3
           2                      0
           3                      0
           4                      0
           5                      0
           6                      0
           7                      0
           8                      0
           9                      0
          10                      0
          11                      0
          12                      0
          13                      0
          14                      0
          15                      0

16 rows selected.

SQL>

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms