Databases Reference
In-Depth Information
The following example shows how the optimizer is always aware of the deleted rows in a table and
makes the correct choice, even when you delete a large percentage of a table's rows. Let's create a simple
table with 100,000 rows and create an index on it.
SQL> create table test as select rownum id, 'Sam' text from dual
2* connect by level <=100000
SQL> /
Table created.
SQL> create index test_idx1 on test(id);
Index created.
SQL>
Run the
analyze index validate structure
statement to check the number of lf_rows and lf_blks.
SQL> analyze index test_idx1 validate structure
SQL> /
Index analyzed.
Query the
INDEX_STATS
view to check the number of deleted leaf rows.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
100000 222 0
SQL>
Delete a large number of rows from the table and run the
analyze index validate structure
command again.
SQL> delete test where id <=99999;
99999 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze index test_idx1 validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
100000 222 99999
SQL>
