Databases Reference
In-Depth Information
Problems with the INDEX_STATS view
There are several problems with the
analyze index ...validate structure
command, including the fact
that the command locks the table until the index is analyzed. The real problem with using the
analyze
index …validate structure
command to determine whether you should rebuild an index is that Oracle
does automatically reuse deleted space in an index in most cases. The following is a simple example that
illustrates this fact.
Create a test table.
SQL> create table reb_test (cust_id number, cust_code number, cust_name
varchar2(20));
Table created.
SQL>
Insert a few test rows (nine rows in this example).
SQL> insert into reb_test select rownum, rownum, 'Groucho Marx' from dual
2 connect by level < 10;
9 rows created.
SQL> commit;
Commit complete.
SQL>
Create an index on the CUST_ID column.
SQL> create index reb_test_cust_id_idx on reb_test(cust_id);
Index created.
SQL>
Delete four of the nine rows from the table.
SQL> delete from reb_test where cust_id in (1,2,3,4);
4 rows deleted.
SQL> commit;
Commit complete.
SQL>
Analyze the index with the
analyze index …validate structure
command.
SQL> analyze index reb_test_cust_id_idx validate structure;
Index analyzed.
SQL>
