DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Index rebuild v/s drop and re-create

  1. #1
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45

    Index rebuild v/s drop and re-create

    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 ?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Drop/create is more complex code than unusable/rebuild, and if you change the indexing then you have to remember change the script also.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    You will also need more tablespace with the rebuild since Oracle builds the index first prior to dropping the old one.
    I remember when this place was cool.

  4. #4
    Join Date
    Jun 2006
    Posts
    259
    I'd drop, then Create (in parallel).

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by ixion
    I'd drop, then Create (in parallel).
    It seems to me that you might mention why you'd do that, hmmm?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Quote Originally Posted by pando
    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hi

    say you have this table and using RBO

    Code:
    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.
    Last edited by pando; 08-08-2006 at 03:49 AM.

  9. #9
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Thanks pando..
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width