deleting many rows from a table

From: UXDBA <unixdba73_at_googlemail.com>
Date: Thu, 14 Jan 2010 02:05:43 -0800 (PST)
Message-ID: <89ec99ee-3cc5-44b0-892d-24087377d901_at_j19g2000yqk.googlegroups.com>



All,

Oracle 9.2.0.8
OS - HP Ux 11.23

We have table T
count(*) = 31414748

table definition

create table T (

  OBJID                    NUMBER,
  DEV                      NUMBER,
  NAME                     VARCHAR2(20 BYTE),
  VALUE                    CLOB,

  CONTEXT_INST2GROUP_INST NUMBER,
  CONTEXT_INST2PROC_INST NUMBER
)

Table segemnt size =
select segment_name, bytes/1024/1024/1024 from user_segments where segment_name='T'
  2 /

SEGMENT_NAME



BYTES/1024/1024/1024

T
35.8789063

select
  2 segment_name
  3 from user_lobs where table_name='T'   4 /

SEGMENT_NAME



SYS_LOB0000027756C00004$$ SQL> select sum(bytes/1024/1024/1024) from user_segments where segment_name in (
  2 'SYS_LOB0000027756C00004$$')
  3 /

SUM(BYTES/1024/1024/1024)


               31.9335938

Now we wanted to delete around 70% rows of this table . a) To save storage space
b) To improve the performance gainst this table.

Questions
1) What would be the best way suggested to delete these records 2) Best way to re-org the table. if
DELETE FROM table
WHERE predicates;
is chosen.
We would like to have minimum downtime.
ALter table T / Online-redifnition? ( we are at 9.2.0.8)

Regards Received on Thu Jan 14 2010 - 04:05:43 CST

Original text of this message