Home » RDBMS Server » Performance Tuning » INDEX_STATS.HEIGHT > 3 (HP UNIX / 9.2.0.7)
INDEX_STATS.HEIGHT > 3 [message #428811] Fri, 30 October 2009 02:55 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
SQL> select height, blocks, name, lf_rows, del_lf_rows from index_stats where height > 3 ;

   HEIGHT    BLOCKS NAME                             LF_ROWS DEL_LF_ROWS
--------- --------- ------------------------------ --------- -----------
        4     43500 SECTORAL_IDX_1                   6818494           0

SQL> alter index SECTORAL_IDX_1 rebuild ;

Index altered.

SQL> analyze index SECTORAL_IDX_1 validate structure ;

Index analyzed.

SQL> select height, blocks, name, lf_rows, del_lf_rows from index_stats ;

   HEIGHT    BLOCKS NAME                             LF_ROWS DEL_LF_ROWS
--------- --------- ------------------------------ --------- -----------
        4     43500 SECTORAL_IDX_1                   6818494           0


I have rebuilt the index because height > 3. After rebuiding also the height remains 4. What could be the reason ? Do I need to rebuild the table?

Brayan.
Re: INDEX_STATS.HEIGHT > 3 [message #428816 is a reply to message #428811] Fri, 30 October 2009 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The reason is that it needs it.
BLEVEL/HEIGHT is NOT an indicator of anything and surely not an indicator to rebuild the index (unlike many people think as they don't understand how indexes work and just dump script on the web).

Regards
Michel

[Updated on: Fri, 30 October 2009 03:43]

Report message to a moderator

Re: INDEX_STATS.HEIGHT > 3 [message #428821 is a reply to message #428811] Fri, 30 October 2009 03:34 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Thanks Michel.

Brayan.
Re: INDEX_STATS.HEIGHT > 3 [message #428822 is a reply to message #428821] Fri, 30 October 2009 03:45 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read Jonathan Lewis's article When Should You Rebuild an Index?

Regards
Michel
Previous Topic: INTERNAL_FUNCTION in execution plan
Next Topic: Single Query suddenly goes slow until restart or change
Goto Forum:
  


Current Time: Sun Sep 25 11:10:50 CDT 2016

Total time taken to generate the page: 0.05939 seconds