deleting many rows from a table
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