Index fragmentation in LMT
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Index fragmentation in LMT

  1. #1
    Join Date
    Oct 2001
    Location
    India
    Posts
    36

    Index fragmentation in LMT

    Hi All,
    In our database all the index and data tablespaces are LMT tablespaces. We are aware that the LMT tablespaces doesnt have fragmentation. But I have read some article, stating that the index objects will have holes while there are frequent deletions in data, and hence they are fragmented and the only way to correct them is to rebuild the indexes. Even the indexes are in the LMT this will happen.

    So just correct me whether my understanding is correct or not.

    As my indexes are in LMT, so if I rebuild them, will there be any use out of that?

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    No need to rebuild, the holes will get used when further inserts occur.
    Rest in peace and let the indexes rest too.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    well if you have holes then there are two possibilites

    a)holes would be reused

    suppose your application inserts data like a ,f,x,z and then you delete some entries say f and x .now you will have holes like this
    a hole hole z however if the application again inserts b and c then these holes would be reused..somethin like a,b,c,z


    b)holes will not be reused

    suppose you have a order entry system where the orderid will be a ever increasing sequence number somethin like 1,2,3,4,5,6,7 now if you delete 2 ,3 ,4 ,5 and your application will now insert only 8 ,9,10 etc then you will have holes like this 1,hole,hole,hole,hole,6,7,8

    so the answer to this question is really application dependent.

    regards
    Hrishy

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Rebuild is good as a part of routine mantainence....& more over it wud defragment...so y not rebuild????????

    Amar: get us detailed exp as to y not rebuild?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    Re: Index fragmentation in LMT

    Hi,
    LMT called as Locally Managed Tablespace is a new feature introduced from Oracle 8i.Basically prior to 8i the only tablespace that you can create was Dictionary managed which meant that any allocation or de-allocation of extents will be tracked by the data dictionary.In 8i the LMT will track the extent allocation and de-allocation by itself.Which means that in every datafile of the LMT tablespace ,there is a bitmap which keeps information about the extents in that datafile.So whenever an extent is allocated or de-allocated ,the bit in the bitmap will reflect the changes,so due to this the recursive calls on the data-dictionary is reduced.Also since LMT keeps track of the extents,the coalesing is done automatically.
    Now regarding your question that your indexes and data is in LMT,indexes always have a high degree of fragmentation when your data is frequently deleted or updated or inserted in the table.So accordingly indexes will also take the changes.Whenever there are high deletions on the tables,the indexes will also have the effect of deletions because of which they become fragmented.
    Eventhough u keep the indexes in the dictionary managed or LMT,the fragmentation of indexes will happen ,so you must rebuild your indexes when it becomes more fragmented.The kind of tablespace use to house the indexes will have a very little impact on the performance.

    Regards,
    Rohit Nirkhe,Oracle/Apps DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I think amar is right..i am sure he would come with a simple test case :-)..he can easily prove that..and he would confirm my theory too :-)

    regards
    Hrishy

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by adewri
    No need to rebuild, the holes will get used when further inserts occur.
    Not always. But holes are normally(?) not so important, the depth of the B-tree is more likely the reason to want to rebuild.

    Rest in peace and let the indexes rest too.
    Good advice - in most cases. (I bet there are more effective things that could be done to improve performance.)

    (War story - index rebuild caused the biggest db trash ever I made - 7.3.4 with parallel option used a random number generator on them!)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Yes if the depth is more than 4 yep sure a a case for rebuild..

    regards
    Hrishy

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by hrishy
    Hi

    Yes if the depth is more than 4 yep sure a a case for rebuild..

    regards
    Hrishy
    can you show me one single case where its more than 3, 3 is also rare...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Amar

    I fully agree with you that those situations are rare..for me beleive it or not i have never rebuilt indexes even for once in my production databases..test yes but that was for different reasons all together

    regards
    Hrishy

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