Index rebuild v/s drop and re-create

[dinesh_hm]

Hi,

For a typical data warehousing application having a few million records
what are the pros and cons of using index rebuild options v/s drop and re-create while inserting /updating recs in bulk

is the index rebuild option really faster in this case ?

[slimdave]

Drop/create is more complex code than unusable/rebuild, and if you change the indexing then you have to remember change the script also.

[Mr.Hanky]

You will also need more tablespace with the rebuild since Oracle builds the index first prior to dropping the old one.

[ixion]

I'd drop, then Create (in parallel).

[slimdave]

I'd drop, then Create (in parallel).

It seems to me that you might mention why you'd do that, hmmm?

[pando]

you have to be careful since recreating might change your queries execution plans if you are using rule optimizer

Personally I would do rebuild with nolog and parallel

[Thomasps]

you have to be careful since recreating might change your queries execution plans if you are using rule optimizer

Pando, if he re-create the index with same properties (column definition, storage clause etc) how the RBO will change the plans? Are you trying to say that Oracle (specially RBO) is that instable in generating plans? Thanks.

[pando]

hi

say you have this table and using RBO

create table t1
(
a number,
b number,
c number
);

create index t1_i1 on t1(a, b);

create index t1_i2 on t1(a, c);

If you have a query such as follows which index will be used?

select *
from t1
where a = :1;

Index t1_i2 will be used.

if time after you recreate the index t1_i1 then t1_i1 will be used instead since it has a more recent creation time. You have just changed some of your queries execution plans!

This would not happen with rebuilds.

[Thomasps]

Thanks pando..