| |
GeekInterview.com > Interview Questions > Oracle > Database Administration
| Print | |
Question: Scenarios for rebuilding indexes
Answer: What are the different scenarios for rebuilding indexes... |
| August 08, 2008 09:11:03 |
#1 |
| chandubtech |
Member Since: July 2008 Total Comments: 1 |
RE: Scenarios for rebuilding indexes
|
Actually you need to rebuild the indexes for 2 main scenarios.
1)deleted entries represents 20% or more of the current entries
2)the index depth is more than 4 levels
Oracle index nodes are not deleted physically when the rows of tables deleted nor the entries are removed from the index.Oracle deletes these index nodes logically and leaves 'dead nodes' in the index tree itself where these deade nodes may be reused if another adjacent entry is required.
how ever when large number of table rows are deleted,there may be chance of deleted leaf nodes will make index fast full scans for a longer period.
Deleted leaf rows:the term deleted leaf rows referred to the number of index inodes that have been deleted logically by oracle when rows of table deleted.
in oracle you must use these commands;
SQL>analyze index index_name compute statistics;
SQL>analyze index index_name validate structure;
SQL>select del_lf_rows/decode(lf_rows,0,1lf_rows) from index_stats where name='index_name';
if the query returns more than 20 %.then you may think of rebuilding the indexes.
or in other way :
after you analyze the above 2 statements ,you may think of rebuild the indexes where height is more than three levels
Index height:The number of levels that index would span when massive inserts happened to tables
Gets Per index access:the number of logical i/o 's required when a row with an index is fetched.
also we need to think of rebuild the indexes when gets per index access is more than 5
example when to rebuild the indexes:
SQL>create table temp_stats as select name,most_repeated_key,distinct_keys,del_lf_rows,height,blk_gets_per_access from index_stats;
SQL> insert into temp_stats (select name ,most_repeated_key ,distinct_keys ,del_lf_rows ,height ,blks_gets_per_access from index_stats);
SQL>select distinct name c1,most_repeated_key c2,distinct_keys c3,del_lf_Rows c4,height c5,blks_gets_per_access c6 from temp_stats where height > 3 or del_lf_rows > 10 order by name; after analyzing the indexes ,if the above query returns any rows then you need to rebuild the indexes. SQL>alter index index_name rebuild tablespace tablespace_name storage (initial new_initial next new_next freelists new_freelist_number )
Though the lengthy but content one
|
| |
Back To Question | |
