Hi
If the pattern of deleted dta leaves you with many almost empty leaf blocks then
you may find that you would benefit from and index COALESCE rather than a
REBUILD.
On the subject of wasted space in table blocks it's very difficult to
generalise. The best way to go may be to analyse the table immediately after the
delete and see how the space is being used. You could then decide on appropriate
table settings.
It's possible to 'rebuild' a table by using ALTER TABLE MOVE . . . though it
does have restrictions so is not universaly applicable.
Would partitioning be an option for you? If you're dropping years of data at a
time then you could drop a whole year's partition at a single stroke. It would
be faster and would release the space immediately.
Hope that some of that helps. I also hope you hadn't already thought of it all =
)
Regards,
Mike Hately
Oracle DBA.
Greg
My concern is How can I reuse the deleted space, I have 20Gb Table with 15Gb
Indexes, So I may not touch the table due to massiveness, I can do atleast
indexes rebuild for availing 3-4Gb back. So in that regard pct_used has
greater significance for reusing the space for future insertions.
Thank you all for your responses and valueble input.
Thanks
Raghu.
>From: Greg Moore <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: PCTUSED??
>Date: Wed, 06 Jun 2001 15:56:25 -0800
>
> > I have big growing tables has default PCT_USED 40%, But we delete data
>some
> > times one year or 2yrs data. What is optimum value for this parameter??
>I
> > may consider my database is oltp. So how can decide a correct value for
> > this???
>
>PCT_USED might not have any effect for you at all.
>
>If you keep inserting rows by date, the blocks will fill with rows where
>the
>date is the same for every row in the block, or close. Then if the only
>time you delete is when you delete an entire year's worth of data, entire
>blocks will become empty. That's going to cause them to go on the free
>list
>no matter what you set PCT_USED to.
>
>As the other reply implied, your main concern may be tables where, after
>the
>mass deletes, there are many blocks below the high water mark that are
>completely empty. This would take up a little disk space until they are
>filled, but the main concern would be that full table scans would take as
>long after the deletes as before, due to the need to read all blocks up to
>the high water mark (empty or full). So if that is a concern you could
>reorg the table.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Greg Moore
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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).
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raghu Kota
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
