Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Rebuilding Index

    Hi,

    I'm doing direct-load INSERT on nonpartitioned tables that have local indexes.
    To avoid the performance impact of index maintenance , I'm dropping the index before the
    INSERT and then rebuilding it afterwards .

    " rebuilding it afterwards " - Does Oracle create a new index or refresh the previous index ?
    I guess that it creats a new index .

    Any suggestions will be highly appreciated ...
    himridul

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152

    Wink

    It will create a brand new one - you've deleted the old one mate
    Regards
    Dbabren

  3. #3
    Join Date
    Jan 2004
    Posts
    99
    I dont understand? please clarify!

    once you drop an index!! you have to recreate it manually!!

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    drop = delete

    When you drop an index/table/view you are deleting it from the database. The object will then have to be recreated manually if you wish to reinstate it.
    Regards
    Dbabren

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Rebuilding Index

    An alternative approach:

    alter index xxx1 unusable;
    alter index xxx2 unusable;
    alter session set skip_unusable_indexes = true;
    insert /*+ append */ into xxx ...;
    alter index xxx1 rebuild;
    alter index xxx2 rebuild;
    alter session set skip_unusable_indexes = false;

    That way the index never goes away.

  6. #6
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    Very useful tip Tony - I'll make a note of that one.

    Thanks
    Regards
    Dbabren

  7. #7
    Join Date
    Jan 2004
    Posts
    66
    Tony , it's really a helpful tip . But I've a doubt regarding this.

    " alter index xxx1 rebuild "
    Will it take count of the incremental data ? Is it a good practice to rebuilding the index ?
    I mean , the index should work on the new inserted data.

    I think , better first drop the index , then insert data into table , and then recreate the index . In this way , the new index can keep track of new inserted data.
    himridul

  8. #8
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    sorry himridul - I've just re-read the third post and realised it wasn't from you!! I replied to that one thinking it was your reply to my original reply. Doh!
    Regards
    Dbabren

  9. #9
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    By the way alter index rebuild does a drop index and recreate under the covers, so the effect is exactly the same
    Regards
    Dbabren

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes it is a good idea. Of course once it is "rebuilt" it will include the newly inserted data, otherwise it would be useless ("unusable"). A "usable" index is one that can be used to get correct results.

    Drop and recreate if you prefer, but not on those grounds.

  11. #11
    Join Date
    Jan 2004
    Posts
    66
    So in both cases (Recreating and Rebuilting) the index , will include the newly inserted data .
    so which one will be faster if I've to impose the index after inserting 100 millions of data into the table.

    1. Drop - Recreate

    2. alter index ind1 unusable;
    alter session set skip_unusable_indexes = true;
    insert /*+ parallel */ into table
    alter index ind1 rebuild;
    alter session set skip_unusable_indexes = false

    Thanks ....
    himridul

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Here is where many dbs go wrong.
    You need to test this stuff.
    You shouldn't take our word for it, but prove it.

    Run a benchmark and see what is best for you.

    In fact, post the results.

    Originally posted by himridul
    So in both cases (Recreating and Rebuilting) the index , will include the newly inserted data .
    so which one will be faster if I've to impose the index after inserting 100 millions of data into the table.

    1. Drop - Recreate

    2. alter index ind1 unusable;
    alter session set skip_unusable_indexes = true;
    insert /*+ parallel */ into table
    alter index ind1 rebuild;
    alter session set skip_unusable_indexes = false

    Thanks ....
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Jan 2004
    Posts
    66
    Poobah , actually I can test this stuff with 1 million of data. Because 100 millions of data are available in the production site . Now I'm in development site , and they are not providing at least 10 millions of data. If I would have 100 millions of data in my hand , then obviously I would test it . For 1 million of data , anything (Rebuilding or Recreating) can be faster.But the result would be different for 100 millions of data.

    That's why I was asking the result , if anyone had worked on this thing .
    Anyway , I will try to manage this stuff .

    Thanks ...
    himridul

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    If peoplpe need to access the data then rebuild the index.
    If not I say drop/recreate.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14
    Tony,

    Why is necessary the "append" hint into the SQL insert statement from your SQL statements list?
    ...
    insert /*+ append */ into xxx ...;
    ...

    Thanks,
    Adrian

Posting Permissions

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