Re: Separate Indexes and Data

A fine response, Richard. Thank you.

One of the guys coding the index stuff was/is Jonathan Klein, and I remember asking him some years ago about reuse of index blocks, and he - at that point - said that he was pretty sure he put the reuse of leaf blocks into 7.1, but that branch blocks didn't get reused. That was then, and I think it has changed since. Not that it matters that much - there are few branch blocks compared to leaf blocks, and it's not often a branch blocks is completely emtied anyway :).

Mogens

Richard Foote wrote:

> 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
> <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
>
> ----- Original Message -----
> *From:* John Kanagaraj <mailto:john.kanagaraj_at_hds.com>
> *To:* Multiple recipients of list ORACLE-L
> <mailto:ORACLE-L_at_fatcity.com>
> *Sent:* Tuesday, October 14, 2003 5:44 AM
> *Subject:* RE: RE: Separate Indexes and Data
>
> Jared,
>
> Any indexes supporting a "In-Today; Gone-Tomorrow" status table
> will require index rebuilds. Most of them have monotonically
> increasing numbers which lends itself to a 'holey' index... (I
> have a bunch of them with Oracle Apps Concurrent Manager and
> Workflow tables)
>
>
> John Kanagaraj
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Disappointment is inevitable, but Discouragement is optional!
>
> ** The opinions and facts contained in this message are entirely
> mine and do not reflect those of my employer or customers **
>
> -----Original Message-----
> *From:* Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
> *Sent:* Monday, October 13, 2003 11:39 AM
> *To:* Multiple recipients of list ORACLE-L
> *Subject:* RE: RE: Separate Indexes and Data
>
>
> hmmm... fodder for an article I've been contemplating.
>
> "Indexes: to rebuild or not to rebuild - that is the question"
>
> There's no need to reclaim space, except in special
> circumstances.
>
> As Kirti pointed out once, a sequentially incrementing numeric
> key is
> possibly one of those circumstances.
>
> Not much point in rebuilding indexes in most cases.
>
> If anyone cares to submit test cases for validation of the
> need of an
> index rebuild, you may do so here.
>
> Give me some test fodder!
>
> Jared
>
>
>
>
>
> *JayMiller_at_tdwaterhouse.com*
> Sent by: ml-errors_at_fatcity.com
>
> 10/13/2003 08:59 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: RE: Separate Indexes and Data
>
>
>
>
> I assume that what Rachel is referring to is the fact that
> indexes will
> generally not release much space when the underlying rows are
> deleted. They
> just keep growing, so if you have a large indexed table that
> frequently
> deletes and inserts the indexes can grow to fairly ridiculous
> sizes over a
> period of time. We just went through the exercise of
> rebuilding indexes on
> a db supporting a 3rd party app and reclaimed about 70% of the
> allocated
> index space.
>
> Jay Miller
> Sr. Oracle DBA
> x68355
>
>
> -----Original Message-----
> Sent: Sunday, October 12, 2003 7:39 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Rachael,
>
> You have me a little confused here.
>
> What do you mean by "We over allocate space" ? To the index
> segments or to
> the tablespace ?
>
> Why the need to rebuild the indexes ? How are they using more
> space than
> required ?
>
> What do you mean that you adjust the pctfree so you can
> determine "how small
> you can resize them to" ?
>
> You seem to go to a lot of trouble, I'm just failing to see
> what it all
> achieves ???
>
> Cheers
>
> Richard
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, October 01, 2003 4:34 AM
>
>
> > Nuh uh, not me... I have never used or experimented with
> > auto-allocate.
> >
> > I separate indexes and tables so that I can reclaim space by
> > rebuilding the indexes into smaller space.
> >
> > I've just completed writing the scripts for the following:
> >
> > we have a data warehouse, partitioned on the biggest table
> on date by
> > month. There are 10 or 11 indexes on this table. We
> overallocate space
> > when we create the new partition for the next month. Data is
> loaded
> > daily. The hosting company has an automated procedure to add
> space to
> > the datafile if the used space percentage is greater than
> some number
> > (we get charged each time they do this, and they never
> allocate enough
> > space so they do it over and over towards the end of the month).
> >
> > since the indexes are increasing on a daily basis, we
> overallocate the
> > space. The next month, I go out, determine the
> > partition/tablespace/datafiles that need to be resized (naming
> > standards rule in this case), rebuild the indexes into an
> interim
> > tablespace, rebuild them back to the original one with a
> smaller
> > pctfree and then determine how small I can resize them down to.
> >
> > If there were table data in these tablespaces, I'd be out of
> luck on
> > trying to reclaim space
> >
> >
> > --- rgaffuri_at_cox.net wrote:
> > > the defrag paper was written back in 1998 I believe.
> Uniform extents
> > > were a good solution pre-9i. We use them here on our 8i
> databases. I
> > > stick with an uniform 5m extent size even though I have
> tables that
> > > can fit into 128k extents, but feel that the overall time
> savings by
> > > using 1 extent size makes up for this.
> > >
> > > unfortunately unlike most systems we cannot break up our
> tables into
> > > different tablespaces. We use transportable tablespaces to
> batch
> > > publish data to data marts. New tablespaces mean additional
> > > transportable tablespaces and more places for stuff to go
> wrong.
> > >
> > > I saw some posts on dejanews recently from some pretty
> experienced
> > > DBAs stating that there may be 'flaws' in auto-allocate
> leading to
> > > poor extent sizes that leads to fragmentation. I believe
> Rachel
> > > Carmichael made a post on here a few months back with the
> similiar
> > > experience(could be wrong). Due to even the 'small' chance
> of flaws
> > > in auto-allocate, Im thinking of waiting for version 10g
> before
> > > using it. Just to be safe. Not worth risking a defrag on a
> > > production system.
> > > >
> > > > From: "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
> > > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > > To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > > > Subject: RE: Separate Indexes and Data
> > > >
> > > > I'd be very interested to know how many people have
> their index
> > > tablespaces on a different backup schedule from their data
> > > tablespaces. If so how different? What happens when a media
> > > failure occurs and you must restore from backup? You
> would need to
> > > have on hand and apply more redo logs to make the
> database current.
> > > >
> > > > I understand the argument proffered is separating
> indexes and data
> > > can mean that when physical corruption of the file happens
> to an
> > > index tablespace then all one needs do is to offline,
> drop, drop and
> > > rebuild the index tablespace. I admit I have not tried
> off-lining
> > > the tablespace first, but you cannot normally drop a
> tablespace
> > > which is being used to enforce referential integrity. If
> off-lining
> > > the tablespace first does work, I can see someone trying
> to do the
> > > rebuild with the database available and having duplicate
> records in
> > > the parent tables and records without parents in the child
> tables.
> > > >
> > > > On the size of the segments: The paper entitled "How To
> Start
> > > Defragmenting and Start Living" or something like that
> strongly
> > > advocated uniform extent sizes, the suggestion sizes were
> 128K, 4M,
> > > 128M, and 4G as I recall. However the paper
> > > > Never mentioned what to do when an object that used to
> fit nicely
> > > into the 128k extent category now more properly belongs
> to the 4M
> > > category. If you move the data, large holes are left in
> the other
> > > tablespace, and while this does not impact Oracle
> performance, it
> > > does mean that your physical backups are larger than
> necessary. I
> > > am in the process of migrating from uniform to autoallocated
> > > extents. This means extents of different sizes share the same
> > > tablespace. The extent sizes being multiples of each
> other. This
> > > removes the argument about not having indexes and data in
> the same
> > > tablespaces due to their different sizes.
> > > >
> > > > Ian MacGregor
> > > > Stanford Linear Accelerator Center
> > > > ian_at_slac.stanford.edu
> > > >
> > > > -----Original Message-----
> > > > Sent: Monday, September 29, 2003 8:10 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Thomas,
> > > >
> > > > It *is* a good idea to separate index data from heap
> data into
> > > different tablespaces. But the reason isn't solely to
> eliminate I/O
> > > competition. Even if I/O competition isn't an issue for
> you (and the
> > > OFA Standard doesn't say that it will be), then it's
> *still* a good
> > > idea to separate your index data from your heap data, for
> reasons
> > > including:
> > > >
> > > > * Index segments have different backup and recovery
> requirements
> > > than their corresponding heap segments. For example, as Peter
> > > mentioned, if you have an index block corruption event,
> then it's
> > > convenient to just offline, kill, and rebuild an index
> tablespace.
> > > If the indexes and data are mixed up in a single
> tablespace, this is
> > > not an option. Another
> > > > example: If you construct your backup schedule to make media
> > > recovery time a constant, then you probably don't need to
> back up
> > > your indexes on the same schedule as you back up your
> heaps. But
> > > unless they're in different tablespaces, this isn't an option
> > > either.
> > > >
> > > > * Index segments are usually smaller than their
> corresponding heap
> > > segments. Using separate tablespaces allows you to use a
> smaller
> > > extent size to conserve disk storage capacity.
> > > >
> > > > I don't think I ever wrote that you need to put indexes
> and their
> > > corresponding tables/clusters on separate disks, but you
> do need to
> > > be
> > > > *able* to do that if your I/O rates indicate that you
> should.
> > > >
> > > > For the original OFA Standard definition, please see
> section 3 of
> > > the document called "The OFA Standard--Oracle for Open
> Systems," and
> > > section 5 of "Configuring Oracle Server for VLDB," both
> available
> > > for free at www.hotsos.com.
> > > >
> > > >
> > > > Cary Millsap
> > > > Hotsos Enterprises, Ltd.
> > > > http://www.hotsos.com
> > > >
> > > > Upcoming events:
> > > > - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
> > > > - Hotsos Symposium 2004: March 7-10 Dallas
> > > > - Visit www.hotsos.com for schedule details...
> > > >
> > > >
> > > > -----Original Message-----
> > > > Thomas Day
> > > > Sent: Monday, September 29, 2003 9:05 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > My struggle is not with the directory layout OFA.
> > > >
> > > > It is with the "mythical" OFA that every DBA that I have
> talked to
> > > knows all about. Where ORACLE says that if you are a good
> and
> > > competent DBA you will separate your table data and your
> index data
> > > into two separate tablespaces so that one disk head can be
> reading
> > > index entries while another disk head is reading the table
> data.
> > > You've never run into that?
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Tim Gorman <tim
> > > >
> > > > @sagelogix.com> To:
> Multiple
> > > > recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >
> > > > Sent by: cc:
> > > >
> > > > ml-errors Subject:
> Re: BAARF
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > 09/28/2003 09:44
> > > >
> > > > PM
> > > >
> > > > Please respond
> > > >
> > > > to ORACLE-L
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Thomas,
> > > >
> > > > Please pardon me, but you are off-target in your
> criticisms of
> > > > OFA.
> > > >
> > > > It has never advocated separating tables from indexes for
> > > performance purposes. Ironically, your email starts to
> touch on the
> > > real reason for separating them (i.e. different types of I/O,
> > > different recovery requirements, etc). Tables and indexes
> do belong
> > > in different tablespaces, but not for reasons of performance.
> > > >
> > > > Cary first designed and implemented OFA in the early 90s and
> > > formalized it into a paper in 1995. Quite frankly, it is a
> > > brilliant set of rules of how Oracle-based systems should be
> > > structured, and a breath of fresh air from the simplistic
> way that
> > > Oracle installers laid things out at the time. It took
> several years
> > > for Oracle Development to see the light and become
> OFA-compliant,
> > > and not a moment too soon either. Just imagine if
> everything were
> > > still installed into a single directory tree under
> ORACLE_HOME? All
> > > of things you mention here have nothing to do with OFA.
> > > >
> > > > Please read the paper.
> > > >
> > > > Hope this helps...
> > > >
> > > > -Tim
> > > >
> > > > P.S. By the way, multiple block sizes are not
> intended for
> > > > performance
> > > > optimization; they merely enable transportable
> > > > tablespaces
> > > between
> > > > databases with different block sizes.
> > > >
> > > >
> > > > on 9/25/03 11:04 AM, Thomas Day at tday6_at_csc.com wrote:
> > > >
> > > > >
> > > > > I would love to have a definitive site that I could
> send all
> > > RAID-F
> > > > > advocates to where it would be laid out clearly,
> unambiguously,
> > > and
> > > > > definitively what storage types should be used for
> what purpose.
> > > > >
> > > > > Redo logs on RAID 0 with Oracle duplexing (y/n)?
> > >
> > === message truncated ===
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > The New Yahoo! Shopping - with improved product search
> > http://shopping.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> (or the
> > name of mailing list you want to be removed from). You may
> also send
> > the HELP command for other information (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Richard Foote
> INET: richard.foote_at_bigpond.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
> services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in the
> message BODY, include a line containing: UNSUB ORACLE-L (or
> the name of
> mailing list you want to be removed from). You may also send
> the HELP
> command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: JayMiller_at_tdwaterhouse.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
> services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
> subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).