Home » RDBMS Server » Server Administration » rebuild index
rebuild index [message #555672] Sun, 27 May 2012 00:09 Go to next message
priteshranjan.preet
Messages: 131
Registered: April 2012
Location: delhi
Senior Member
In which condition it is required to rebuild the index and how can identified it?

thanks & regards
pritesh ranjan
Re: rebuild index [message #555673 is a reply to message #555672] Sun, 27 May 2012 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are only few reasons (and the worst one you'll find somewhere is to do it every X days).
2 of these reasons for instance:
- When you did a bulk delete and don't want to reinsert data (archiving or purging)
- When some direct path load invalidated them

Regards
Michel

[Updated on: Sun, 27 May 2012 01:12]

Report message to a moderator

Re: rebuild index [message #555740 is a reply to message #555673] Mon, 28 May 2012 02:10 Go to previous messageGo to next message
SSharma
Messages: 17
Registered: July 2011
Location: INDIA
Junior Member
Indexes are rebuilt when
- There is fragmentation in index
- Index is growing in sizes and the deleted space is not reused .

You can find the eligible index candidates to rebuild as per this :

1) Height i.e (BLEVEL + 1) >4
2) Ratio of deleted entries to total leaf entries is greater than 20 ;

First analyze the index :
Analyze index index_name validate staructure ;

Can find height using dba_indexes and the deleted/total leaf ratio from index_stats view .

Thanks,
Saurabh
Re: rebuild index [message #555745 is a reply to message #555740] Mon, 28 May 2012 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Indexes are rebuilt when
- There is fragmentation in index


Define index fragmentation; how to check it?

Quote:
Index is growing in sizes and the deleted space is not reused


In which case? how to check it?

Quote:
1) Height i.e (BLEVEL + 1) >4


what does this measure? Why is this a measure that you have to rebuild?

Quote:
2) Ratio of deleted entries to total leaf entries is greater than 20 ;


Why? Explain!

Regards
Michel

Re: rebuild index [message #555910 is a reply to message #555745] Tue, 29 May 2012 20:04 Go to previous messageGo to next message
SSharma
Messages: 17
Registered: July 2011
Location: INDIA
Junior Member
Fragmentation in index/Index is growing in sizes and the deleted space is not reused :

Occurs when there are frequent deletes/inserts on a table . As a result its possible that leaf entries are deleted and they are not reused . A position comes when this ratio (del_lf_rows/lf_rows)*100 exceeds 30 . At that time you need to rebuild index

[Updated on: Mon, 04 June 2012 13:15] by Moderator

Report message to a moderator

Re: rebuild index [message #555911 is a reply to message #555910] Tue, 29 May 2012 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> A position comes when this ratio (del_lf_rows/lf_rows)*100 exceeds 30 . At that time you need to rebuild index
why is 30 the decision point? Why not 25, 35, 20, 40, etc?

post SQL & results that quantify the measurable performance gained by doing index rebuild.
Re: rebuild index [message #555914 is a reply to message #555911] Tue, 29 May 2012 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>del_lf_rows
only exists within INDEX_STATS view.

Reading The Fine Manual

http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5138.htm#REFRN29025

it says "INDEX_STATS stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.

Note:
The ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE statement must be used in order to collect statistics"

>DEL_LF_ROWS NUMBER Number of deleted leaf rows in the index
Can this value ever decrease for any specific index (when leaf blocks/rows are reused)?
Re: rebuild index [message #556488 is a reply to message #555914] Mon, 04 June 2012 13:08 Go to previous messageGo to next message
priteshranjan.preet
Messages: 131
Registered: April 2012
Location: delhi
Senior Member
please also clear that what is the need of create a secondary index on an index organized table and how can i identified it,what is its benifits?

regards
pritesh ranjan
Re: rebuild index [message #556489 is a reply to message #556488] Mon, 04 June 2012 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>please also clear that what is the need of create a secondary index on an index organized table
there is no such need
Re: rebuild index [message #559101 is a reply to message #556489] Thu, 28 June 2012 14:04 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I followed all the recommendations by Oracle as to when to rebuild indexes but found out that if you rebuild all indexes online and rerun statistics on them, that some queries will run more optimal, reducing the load on the db.
SQL > alter index scott.pk_emp rebuild nologging online;

Index altered.

SQL > alter index scott.pk_emp logging;

Index altered.

SQL > execute dbms_stats.gather_index_stats('SCOTT','PK_EMP',estimate_percent=>100)

PL/SQL procedure successfully completed.

I use the following query to monitor how much memory I am processing per day to see how much benefit the index rebuilds were:
ECSCDAP1P > @dba_hist_seg_stat_AVERAGE_LOGICAL_READS_PER_HOUR.sql

DATE AVG_GIG_PER_MIN
-------------------- ---------------
2012-06-19 Tuesday 818.2
2012-06-20 Wednesday 779.9 All indexes rebuilt at midnight.
2012-06-21 Thursday 369.3 Usage the next day drops
2012-06-22 Friday 343.8 Remains low the following days.
2012-06-23 Saturday 322.7
2012-06-24 Sunday 321.7
2012-06-25 Monday 367.4
2012-06-26 Tuesday 356.3
2012-06-27 Wednesday 358.5

9 rows selected.

ECSCDAP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.LOGICAL_READS_DELTA)*8192/1024/1024/1024/60/24 Avg_GIG_PER_min
3 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
4 where a.object_id=b.OBJ#
5 and c.snap_id=b.snap_id
6 and c.begin_interval_time > trunc(sysdate-29)
7 and c.begin_interval_time < trunc(sysdate)
8 and b.instance_number=c.instance_number
9 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
10* order by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')

By monitoring the logical gigabytes pressed per minute, I can see the usage grow over several months and I know that I have to rebuild ALL the application indexes.

I have used snapshots of v$segment_statistics when cpu was too high to see which objects were being processed the most, rebuilt all the indexes on the tables being processed the most, ran stats on these indexes and the cpu usage dropped back to normal.

SQL > select owner, index_name from dba_indexes where table_name='EMP';

OWNER INDEX_NAME
------------------------------ ------------------------------
SCOTT PK_EMP

Alan
Re: rebuild index [message #559102 is a reply to message #559101] Thu, 28 June 2012 14:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALAN,

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: rebuild index [message #559116 is a reply to message #559102] Thu, 28 June 2012 15:51 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I got it, I use code and /code inclosed in square brackets. I will fix my other posts.
 asdf
  asdf
   asdf
  asdf
 asdf
Re: rebuild index [message #559121 is a reply to message #559116] Thu, 28 June 2012 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And don't forget to indent the code and queries, adding code tags is not sufficient to make a query readable.

Regards
Michel

[Updated on: Thu, 28 June 2012 23:16]

Report message to a moderator

Re: rebuild index [message #617649 is a reply to message #559121] Wed, 02 July 2014 09:23 Go to previous messageGo to next message
suhasdba
Messages: 141
Registered: April 2013
Location: INDIA
Senior Member
Michel,

Quote:
And don't forget to indent the code and queries

what does this mean ? Can you show me one example ?

Regards,
Suhas

[Updated on: Wed, 02 July 2014 09:24]

Report message to a moderator

Re: rebuild index [message #617652 is a reply to message #617649] Wed, 02 July 2014 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
suhasdba wrote on Wed, 02 July 2014 07:23
Michel,

Quote:
And don't forget to indent the code and queries

what does this mean ? Can you show me one example ?

Regards,
Suhas


please read http://www.orafaq.com/forum/t/174502/102589/
Re: rebuild index [message #617660 is a reply to message #555910] Wed, 02 July 2014 10:06 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Looks like you need to spend some time with Richard Foote.

Go to http://richardfoote.wordpress.com/articles-demos/ and read everything you see when searching 'rebuild'.
Re: rebuild index [message #617720 is a reply to message #617652] Thu, 03 July 2014 00:28 Go to previous message
suhasdba
Messages: 141
Registered: April 2013
Location: INDIA
Senior Member
Thank you .Now i understood how to indent the code and queries and format the code tags.
Previous Topic: ORA-02019
Next Topic: How do you rename User name and switch user to a new tablespace?
Goto Forum:
  


Current Time: Fri Mar 29 07:39:30 CDT 2024