Re: defrag needed

In <3b7fa683.2160498_at_news.mobilixnet.dk> plovmand_at_hotmail.com (Kenneth Koenraadt) writes:

>Hi Konstantin,

>You face a very common problem with data reorganisation. Deleting rows
>from a table keeps ALL the storage, and loading with direct = true
>does not use the existing blocks (even if the blocks are empty),
>instead it loads above the high-water mark. Result - your table
>becomes more and more fragmented over time.

>2 solutions :

>1)Reorganise the table by

> a) create table BACKUP as (select * from ORIGINAL_TABLE);
> b) drop table ORIGINAL_TABLE;
> c) ALTER TABLE BACKUP RENAME TO ORIGINAL_TABLE;

>2) PARTITIONING the table (by the date field) could be very good idea.
>Then cut away the partition with the rows that have aged out.

>Solution 1) is simple, but expensive regarding I/O.
I do this more or less now once a week.

>Solution 2) is more elegant, more flexible and less costly.

>IMHO, partitioning is a *very* powerful DBA feature. So don't hesitate
>to learn AND use it.

That sounds so to me, too. Could You give me a hint, if entering 'partitioning' into technets search engine would be enough?

Konstantin

>Regards,
>Kenneth Koenraadt
>Systems Consultant
>Oracle DBA
>plovmand@<no-spam>hotmail.com

>On 18 Aug 2001 10:23:47 +0200, elwood_at_news.agouros.de (Konstantinos
>Agouros) wrote:

>>Hi,
>>
>>I have the following problem (8.1.6.0.3 on Solaris7):
>>Once in the night I import roughly 2-5mio entries using sqlldr direct=true.
>>After some work with the data I delete all of this data that's older than
>>two days. So this one big table stays at about 10mio lines. I set my datafile
>>to autoextend and it keeps growing nevertheless. I guess the directpath method
>>simply appends to the end. So is there a way besides exporting/dropping/import-
>>ing all tables to keep the datafile tight?
>>
>>Konstantin
>>--
>>Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood_at_agouros.de
>>Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
>>----------------------------------------------------------------------------
>>"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres

-- 
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood_at_agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres