Re: Rebuild Indexes
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Feb 2004 20:20:45 +0200
We'll I've been lucky enough that my FND_CONCURRENT_REQUESTS tables indexes
haven't been growing that much (I usually delete 2 months old data every
week). I haven't checked which indexes are on that table, but I assume that
there are ones on concurrent request ID which is continuously increasing.
But when I delete all entries older than 2 months, I'll be deleting lot's of
of leaf blocks completely empty. And completely empty leaf blocks are put in
free list, so they can be reused by subsequent inserts with new ID values.
There is an issue with branch blocks because they never get reused for
another value range than originally allocated for, but since branch blocks
are small part of an index anyway (branch blocks are key compressed), I've
seen no real index growth problem on this particular table.
Tanel.
----- Original Message -----
From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, February 18, 2004 6:59 PM
Subject: RE: Rebuild Indexes
> Naveen,
>
> I believe the problem is not with a monotonically increasing index (which
> should NOT be rebuilt as it is of no use) - rather it is with a
> monotonically increasing index that is also being _deleted. This is
typical
> of indexes on FIFO-type tables such as the FND_CONCURRENT_REQUESTS in Apps
> (any version). In the latter case, you should perform a 'one-time' index
> rebuild of such monotonically-increasing, followed by regular COALESCE of
> the indexes to 'reclaim' 'lost' space. Jonathan/Richard Foote might have
> something to add on this....
>
> John Kanagaraj <><
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Listen to great, commercial-free christian music 24x7x365 at
> http://www.klove.com
>
> ** The opinions and facts contained in this message are entirely mine and
do
> not reflect those of my employer or customers **
>
> >-----Original Message-----
> >From: oracle-l-bounce@xxxxxxxxxxxxx
> >[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Naveen,
> >Nahata (IE10)
> >Sent: Tuesday, February 17, 2004 11:24 PM
> >To: oracle-l@xxxxxxxxxxxxx
> >Subject: RE: Rebuild Indexes
> >
> >
> >John,
> >
> >Would it be advisable to go for Index rebuilds in case of
> >index based on
> >monotonically increasing columns? As much as I understand (and
> >I must admit,
> >i don't know much about the internals of indexes), in case of
> >such indexes
> >the problem of 'leaf block split' should not happen.
> >
> >Regards
> >Naveen
> >
> >>-----Original Message-----
> >>From: John Kanagaraj [mailto:john.kanagaraj@xxxxxxx]
> >>Sent: Wednesday, February 18, 2004 12:30 PM
> >>To: 'oracle-l@xxxxxxxxxxxxx'
> >>Subject: RE: Rebuild Indexes
> >>
> >>
> >>Vidya,
> >>
> >>If you are collecting (and storing) STATSPACK data, you might want to
> >>baseline V$SYSSTAT values and look for spurts in 'leaf node
> >>splits' after
> >>rebuilds....
> >>
> >>John Kanagaraj <><
> >----------------------------------------------------------------
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >----------------------------------------------------------------
> >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> >put 'unsubscribe' in the subject line.
> >--
> >Archives are at //www.freelists.org/archives/oracle-l/
> >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> >-----------------------------------------------------------------
> >
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
