Home » RDBMS Server » Performance Tuning » Index rebuild
Index rebuild [message #269548] Sun, 23 September 2007 04:30 Go to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
How can I rebuild index faster in oracle 10gr2.
is it useful???
alter index iname rebuild nologging parallel 6;
Re: Index rebuild [message #269553 is a reply to message #269548] Sun, 23 September 2007 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
is it useful???

Almost never.

But it is risky and can lead to bad performances.

Regards
Michel
Re: Index rebuild [message #269555 is a reply to message #269548] Sun, 23 September 2007 06:16 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
NOLOGGING and PARALLEL both r risky?????
Re: Index rebuild [message #269558 is a reply to message #269555] Sun, 23 September 2007 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
rebuild with any option is risky.

Regards
Michel
Re: Index rebuild [message #269621 is a reply to message #269548] Sun, 23 September 2007 21:51 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
thanx michel.
Index rebuild criteria [message #269671 is a reply to message #269548] Mon, 24 September 2007 01:26 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
sorry for posting same thing in two section.

what r the criteria for rebuilding an Index???
I read something like...which have more height or something.
can any1 plz help me abt this matter??

thanx
Re: Index rebuild criteria [message #269673 is a reply to message #269671] Mon, 24 September 2007 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no criteria to rebuild an index.
There are very few cases this can be useful although most often shrink is sufficient. These cases are not defined by index properties by how you use them that is functional properties.

Regards
Michel
Re: Index rebuild [message #269676 is a reply to message #269548] Mon, 24 September 2007 01:34 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
what r the criteria for rebuilding an Index???

Rebuild index is necessary if you want to change tablespace of the intended index or change storage/tablespace parameters or to reduce fragmentation.

Quote:
I read something like...which have more height or something.


Yes, rebuild index can /may not shrink heights.
Re: Index rebuild [message #269677 is a reply to message #269548] Mon, 24 September 2007 01:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Except for changing tablespace and /or changing storage parameter I always prefer coalesce the index.
Re: Index rebuild [message #269680 is a reply to message #269676] Mon, 24 September 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
or to reduce fragmentation

What is index fragmentation?
This is a delusion.
When you are not doing an exceptional operation (like massive purge), index has a natural fragmentation degree. Whatever you do, it will tend to do back to this degree but it does it at coast (split blocks and internal reorganisation on the fly).
So each time you rebuild an index, you tend to decrease performances.

Regards
Michel
Re: Index rebuild [message #269684 is a reply to message #269548] Mon, 24 September 2007 01:48 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
index has a natural fragmentation degree.

I believe. But exceptional also has a degree. Laughing As you specified exceptional operation (like massive purge) as well as robust increase of it can do fragmentation. Yet I prefer always coalesce the index rather than rebuild.

Re: Index rebuild [message #269688 is a reply to message #269548] Mon, 24 September 2007 02:09 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
i think ...i dont need any rebuilding. but whats this???

you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries.

Re: Index rebuild [message #269689 is a reply to message #269548] Mon, 24 September 2007 02:10 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
anyway...i have another query.
how can I get data in index_stats. I analyzed them...


thanx
Re: Index rebuild [message #269690 is a reply to message #269548] Mon, 24 September 2007 02:12 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
you may want to consider rebuilding any index where the height is more than three levels,

May not.
Re: Index rebuild [message #269691 is a reply to message #269548] Mon, 24 September 2007 02:14 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm
Re: Index rebuild [message #269699 is a reply to message #269691] Mon, 24 September 2007 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Keep away from this site.
Many bullshits in it.

Regards
Michel
Re: Index rebuild [message #269831 is a reply to message #269699] Mon, 24 September 2007 15:35 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello tanmoy1048,


Michel Cadot is absolutely right regarding this site.

I prefer this site:

http://www.jlcomp.demon.co.uk/indexes_i.html

Regards,



mson77
Previous Topic: how to find code causing redo to generate
Next Topic: I have one problem with snapshot date
Goto Forum:
  


Current Time: Tue Dec 03 06:07:54 CST 2024