OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Index rebuild

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index rebuild [message #269548]

Sun, 23 September 2007 04:30

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;

Report message to a moderator

Re: Index rebuild [message #269553 is a reply to message #269548]

Sun, 23 September 2007 05:49

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

is it useful???

Almost never.

But it is risky and can lead to bad performances.

Regards
Michel

Report message to a moderator

Re: Index rebuild [message #269555 is a reply to message #269548]

Sun, 23 September 2007 06:16

tanmoy1048
Messages: 133
Registered: June 2007

Senior Member

NOLOGGING and PARALLEL both r risky?????

Report message to a moderator

Re: Index rebuild [message #269558 is a reply to message #269555]

Sun, 23 September 2007 06:57

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

rebuild with any option is risky.

Regards
Michel

Report message to a moderator

Re: Index rebuild [message #269621 is a reply to message #269548]

Sun, 23 September 2007 21:51

tanmoy1048
Messages: 133
Registered: June 2007

Senior Member

thanx michel.

Report message to a moderator

Index rebuild criteria [message #269671 is a reply to message #269548]

Mon, 24 September 2007 01:26

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

Report message to a moderator

Re: Index rebuild criteria [message #269673 is a reply to message #269671]

Mon, 24 September 2007 01:30

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: Index rebuild [message #269676 is a reply to message #269548]

Mon, 24 September 2007 01:34

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.

Report message to a moderator

Re: Index rebuild [message #269677 is a reply to message #269548]

Mon, 24 September 2007 01:36

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.

Report message to a moderator

Re: Index rebuild [message #269680 is a reply to message #269676]

Mon, 24 September 2007 01:41

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: Index rebuild [message #269684 is a reply to message #269548]

Mon, 24 September 2007 01:48

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.

Report message to a moderator

Re: Index rebuild [message #269688 is a reply to message #269548]

Mon, 24 September 2007 02:09

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.

Report message to a moderator

Re: Index rebuild [message #269689 is a reply to message #269548]

Mon, 24 September 2007 02:10

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

Report message to a moderator

Re: Index rebuild [message #269690 is a reply to message #269548]

Mon, 24 September 2007 02:12

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.

Report message to a moderator

Re: Index rebuild [message #269691 is a reply to message #269548]

Mon, 24 September 2007 02:14

tanmoy1048
Messages: 133
Registered: June 2007

Senior Member

http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm

Report message to a moderator

Re: Index rebuild [message #269699 is a reply to message #269691]

Mon, 24 September 2007 02:38

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Keep away from this site.
Many bullshits in it.

Regards
Michel

Report message to a moderator

Re: Index rebuild [message #269831 is a reply to message #269699]

Mon, 24 September 2007 15:35

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 01 02:49:52 CDT 2024