Hi All,
I'm having all sorts of problems getting these emails in a logical order (if at all). This is the first post on this subject I've received since I posted to Rachael, I haven't even received my own post yet !! Anyways, going back in order First to John, no, not all monotonically "here today, gone tomorrow" indexes require rebuilding. Note that fully "emptied" index blocks get placed on the freelist and are fully reusable by subsequent index splits. Therefore if you perform batch deletes over a specific period whereby most deleted entries fully empty a range of index nodes, then frequent rebuilding is highly questionable. Yes, Index Scans/Fast Full Index Scans etc. could be impacted in the interim, it kinda depends on *when* the same volume of data is to be reinserted. Jared, please do write your article (the more solid articles out there the better)!! However note that Jonathan Lewis has written a couple of nice articles over at www.dbazine.com regarding some truths about indexes and index rebuilding. Unfortunately the same site hosts truly awful articles by John Weeg and Mike Hordila who both promote some shocking untruths/myths regarding indexes (that Oracle indexes become unbalanced, that deleted space is never reused, that 4 extents is sufficient for an index, etc. etc.) so one needs to exercise caution when reading stuff from there. Jay, note that indexes generally *do* release space from deleted entries !! Deleted space from a index node within the current index structure can be totally reused by subsequent inserts. And as mentioned earlier, fully emptied blocks can be reused by subsequent index block splits. The requirement to rebuild an index is *extremely rare*. This subject has been raised a number of times recently on the Oracle newgroups (eg. http://groups.google.com/groups?q=g:thl4040185351d&dq=&hl=en&lr=&ie=UTF-8&selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au ). It feels like fighting a lossing battle but one can only try. Yes bulk deletes without subsequent re-inserts or
without re-inserts within a "reasonable" period requires both table and hence
index rebuilds (to reset HWMs). Yes *sparse* deleting of *monotonically*
increasing index entries might require index rebuilds (or coalescing) to compact
index structure for both range scan and fast full index scans. But these are
generally *exceptions*, not the norm.
Hope this mail makes it ??
Cheers
Richard
|
Title: Message
- RE: Separate Indexes and Data Mark Leith
- RE: RE: Separate Indexes and Data Connor McDonald
- RE: RE: Separate Indexes and Data MacGregor, Ian A.
- RE: Separate Indexes and Data Jared Still
- Re: RE: Separate Indexes and Data Richard Foote
- RE: RE: Separate Indexes and Data JayMiller
- RE: RE: Separate Indexes and Data Jared . Still
- RE: RE: Separate Indexes and Data John Kanagaraj
- RE: RE: Separate Indexes and Data Rachel Carmichael
- Re: RE: Separate Indexes and Data Rachel Carmichael
- Re: Separate Indexes and Data Richard Foote
- Re: Separate Indexes and Data Mogens Nørgaard