Mega Code Archive

Analyze index

SQL> SQL> create table indextest as select * from dba_objects 2 where owner in ('OUTLN','PUBLIC','SCOTT','SYS','SYSTEM'); Table created. SQL> SQL> SQL> create index indxtest_objname_idx 2 on indextest (object_name) 3 pctfree 0; Index created. SQL> SQL> analyze table indextest compute statistics; Table analyzed. SQL> SQL> analyze index indxtest_objname_idx validate structure; Index analyzed. SQL> SQL> select name, height, lf_blks, pct_used 2 from index_stats; NAME HEIGHT LF_BLKS PCT_USED ------------------------------ ---------- ---------- ---------- INDXTEST_OBJNAME_IDX 2 36 97 SQL> SQL> SQL> insert into indextest (owner, object_name) 2 values ('a','a'); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> analyze index indxtest_objname_idx validate structure; Index analyzed. SQL> SQL> SQL> select name, height, lf_blks, pct_used 2 from index_stats; NAME HEIGHT LF_BLKS PCT_USED ------------------------------ ---------- ---------- ---------- INDXTEST_OBJNAME_IDX 2 36 97 SQL> SQL> insert into indextest (owner, object_name) values ('ZZZZZ','_ZZZZZZZZZZZ'); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> analyze index indxtest_objname_idx validate structure; Index analyzed. SQL> SQL> SQL> select name, height, lf_blks, pct_used 2 from index_stats; NAME HEIGHT LF_BLKS PCT_USED ------------------------------ ---------- ---------- ---------- INDXTEST_OBJNAME_IDX 2 36 97 SQL> SQL> alter index indxtest_objname_idx rebuild pctfree 10; Index altered. SQL> SQL> analyze index indxtest_objname_idx validate structure; Index analyzed. SQL> SQL> drop table indextest; Table dropped. SQL> SQL>