Which one is better? Recreating or Rebuilding Index

[samdba]

Hi Guys,

Which one is better?

Recreating (Dropping and Recreating) index
or
Rebuilding Index using alter index rebuild online... syntax?

One more question: Is it possible to defragment table (changing INIT & NEXT EXTENT) without dropping it?

Pl. share your views.

Thanks in Adv.

[jlakhani]

I think rebuilding the index online is faster and it doesnt lock your object. You can recreate the index but it will lock the table on which the index gets created.

Regarding Defrag questions
I dont think Oracle uses the word "Defrag" if you are refering to fragmentation then yes there are ways. 1st, analyze table compute statistics then is to look at your chain_cnt in dba_tables. If the value is high then you do have fragmentation. The Metalink can give you more info on fragmentations and how to correct them.

[slimdave]

I agree on the rebuilding, rather than dropping/recreating -- it is also safer because there's less to go wrong.

Best defrag method would be to move the table to a locally managed tablespace with uniform extent size.

[samdba]

Thanks much both of you guys! But tell me one thing that REBUILD and DROP INDEX, they both are same in terms of the result and effect?

[Mr.Hanky]

Wouldn't re-building to a different tablespace be best, faster and avoids fragmentation, no?

MH

[samdba]

Originally posted by Mr.Hanky
[B]Wouldn't re-building to a different tablespace be best, faster and avoids fragmentation, no?

Hi MH,

Wouldn't re-building to a different tablespace be best,faster...

I really did not understand this part. What is the problem in rebuilding in its own tablespace?

Thanks,

[Mr.Hanky]

Well, my understanding is that if the tablespace is already fragmented and you re-build into it, you will be creating a new, fragmented index. Perhaps one of the seniors can clarify this for me?

I know I have a schema that does huge updates and deletes, the indexes get very fragmented after a few weeks. I created another tablespace and I have a script so I can toggle the index re-build between the two. The savings is huge, the used tablespace can go from 2.3 gigs used to 1.2 gigs used after the rebuild. There are no statistics for 90% of these tables so an analyze is not even that neccassary.

MH

[slimdave]

Originally posted by Mr.Hanky
I know I have a schema that does huge updates and deletes, the indexes get very fragmented after a few weeks. I created another tablespace and I have a script so I can toggle the index re-build between the two. The savings is huge, the used tablespace can go from 2.3 gigs used to 1.2 gigs used after the rebuild. There are no statistics for 90% of these tables so an analyze is not even that neccassary.

What kind of fragmented do you mean, Hanky? Do the indexes settle down to a steady-ish size after a week or so?

[samdba]

Originally posted by Mr.Hanky
and I have a script so I can toggle the index re-build between the two.

MH,

We have Apps database and indexes have to be in a particular schema. Pl. tell me how I can bring newly rebuilt index back from a temp tablespace to its original ts.

Thanks,

[slimdave]

How did a rebuilt index get into a temp tablespace?

You specify the TS in the rebuild statement -- miss it out and it rebuilds in it's current TS.