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:[EMAIL PROTECTED]> *To:* Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]> *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:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *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
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
10/13/2003 08:59 AM Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]> 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
>
>
> --- [EMAIL PROTECTED] 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." <[EMAIL PROTECTED]>
> > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> > > 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
> > > [EMAIL PROTECTED]
> > >
> > > -----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 <[EMAIL PROTECTED]>
> >
> > > 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
