analyze index : Analyze « SQL Plus « Oracle PL / SQL

1. Oracle PL / SQL
2. SQL Plus
3. Analyze

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>

   
  

Related examples in the same category