Re: massive delete

From: Chetan Wagle <chetanw_at_yahoo.com>
Date: Fri, 24 Sep 1999 17:22:50 +0530
Message-ID: <7sfojp$99j$1@herald.ctp.com>

Hi Jorge,

1. I'm sorry about the English problem. Happy to hear that my solution was of use to you.
2. Although dropping and re-creating the table would also free up the space, there will be two problems with this :

   Let us say that table is defined something like :

        create table massive_table (num number, vchar varchar2(50),........)
        storage (initial 500M next 1000M minextents 10 pctincrease 25);

          - If you drop this table, it will take longer time than truncate
since entries from the data dictionary also have to go.
          - When you recreate tje table, entries have to be re-entered into
the data dictionary meaning fragmentation there and more work too !!
          - If you try to recreate thre table, you will need a whole lot of

contiguous space which you might noy have and Oracle won't allow you to even create the table.

HTH,
Chetan

Jorge wrote in message <7sfk9m$chs$1_at_diana.bcn.ttd.net>...
>> I am not quite clear about your reply :-(
>
>Excuse me, English isn't easy for me O:-)
>
>>1. Is the solution no 1 helpful to you or not ?? The only idea behind
>using
>
>Yes, it is. (thanks again :-)
>Finally I've done something like this:
>
>select count(*)
> into :n
> from table_1
> where field1= xxxx and field2= yyyy;
>
>for( i=0; i<n/10000 + 1; i++ )
>{
> delete table_1 where field1= xxxx and field2= yyyy AND ROWNUM<10001;
> commit;
>}
>
>
>>2. Truncate is a DDL command that frees all extents from the table and
>>drops the high-watermark too, thus freeing up space which would probably
>>lead to fragmentation on your disk.
>
>Ok. what about dropping the table instead of truncate it to avoid
>fragmentation?
>(suppose a table without constraints)
>
>thanks
> Jorge
>
>
Received on Fri Sep 24 1999 - 06:52:50 CDT