Results 1 to 8 of 8

Thread: Index problem

  1. #1
    Join Date
    Oct 2005
    Posts
    39

    Unanswered: Index problem

    Experts,

    I am working with Oracle 8.1.7.4.0. I went through many posts on internet, which suggest that we should avoid doing index rebuilding but at no place I could see some suggestion.

    I have two tables and 3 index on these tables. Heavy insert/delete operations are taking place on these tables. These indexes are growing in size day by day. What are the areas one should look for possible problems for the growth of Index size ?

    Is it advisable to go for the index rebuilding or one should look for something else before deciding on index rebuilding...??

    Regards, Raj

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would suggest you try rebuilding it in a quiet period (with the online option) and seeing if you actually get a useful amount of space back. You can use analyze with the validate structure option to find out the number of deleted rows but its debatable as to wether that is useful in terms of identifying if the index will benefit from a rebuild.

    On our app we find that an index rebuild shrink by about 35% due to the way the app inserts and then updates the records in a particular table.

    Alan

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also oracle will automatically reuse the space of deleted leaf objects when it can. I have found that you can get space back from the index, but if the file has a lot of inserts and deletes the indexes will regrow back to where they were anyway, so what's the point. Just my .02 cents.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Beilstwh, what you say probably holds for the majority of apps but there are special cases where it doesnt hold. The reason our app benefits from an index rebuild is because a user submits a large volume of data for a particular date and the date is part of the PK index (which the app unfortunately inserts and then updates the same rows). Subsequently the data will never be resubmitted for that date and thus the gaps in the indexes are never refilled.

    As with all things Oracle try it and see if you get benefits (which last), if you dont then dont bother.

    Alan

  5. #5
    Join Date
    Jan 2007
    Location
    kansas City, mo
    Posts
    5

    Index problem

    From observation of a fairly active database, any time you delete over 5% of data on a table, especially large tables, performance will improve with an index rebuild.

  6. #6
    Join Date
    Oct 2005
    Posts
    39
    Quote Originally Posted by chiefdba
    From observation of a fairly active database, any time you delete over 5% of data on a table, especially large tables, performance will improve with an index rebuild.
    Thanks for reply...it really helped me a lot.

    One more question for my knowledge, what should be the size ratio between Index tablespace and Data Tablespace ?? i.e. when i create a database, how much space should allocate to index tablespace corresponding to say 3 GB (For expample) of data tablespace ??


    Regards, Rajeev

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It doesn't really matter - as indexes grow, you can easily add another datafile to the index tablespace. If it means anything to you, ratio of tablespace size in one of our databases is DATA : INDEX = 3 : 2.

  8. #8
    Join Date
    Jan 2007
    Location
    kansas City, mo
    Posts
    5
    Quote Originally Posted by bw1tmar0
    Thanks for reply...it really helped me a lot.

    One more question for my knowledge, what should be the size ratio between Index tablespace and Data Tablespace ?? i.e. when i create a database, how much space should allocate to index tablespace corresponding to say 3 GB (For expample) of data tablespace ??


    Regards, Rajeev


    The best answer I can give to that is it depends. I usually start by making index tablespaces the same size as data tablespaces, since while indexes contain less data, we usually have more of them. That can vary a lot if the table has many columns that you dont index, or a lot of nulls.

Posting Permissions

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