Re: defrag needed

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

1. create table BACKUP as (select * from ORIGINAL_TABLE);
2. drop table ORIGINAL_TABLE;
3. ALTER TABLE BACKUP RENAME TO ORIGINAL_TABLE;
4. 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.

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.

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
Received on Sun Aug 19 2001 - 06:44:11 CDT