索引分析后index_stats里没数据？

来源：互联网发布：施耐德m258编程软件编辑：程序博客网时间：2024/04/30 05:12

SQL> analyze index inp.index_name validatestructure online;

Index analyzed.

SQL> select * fromindex_stats
2 ;

no rows selected

解释如下，要用DBMS_STATS包来分析，另外不加online是会有分析数据的

Analyze Index Validate StructureOnline Does Note Populate INDEX_STATS (文档 ID283974.1)

转到底部

In thisDocument

SymptomsCauseSolution

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3[Release 9.2 to 11.2]
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 notpopulate index_stats or index_histogram while without the onlineclause 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_SPACEUSED_SPACE   PCT_USEDROWS_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>

0 0