deleting many rows from a table
in [Oracle]
|
From: UXDBA on 14 Jan 2010 05:05 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
From: Gints Plivna on 14 Jan 2010 07:53 How about: 1) CREATE table t1 with the same structure and necessary storage definitions 2) INSERT /*+ append */ INTO t1 select only necessary rows 3) drop old table t 4) RENAME t1 to t; Of course it means, that all privileges should be regranted and dependant procedural units recompiled. Minimum downtime (only steps 3 and 4) - the only problem is need for extra space and recompile units/regrant privileges. Gints Plivna http://www.gplivna.eu
From: UXDBA on 14 Jan 2010 08:06 Thanks Gints . But Live table T will have ongoing transaction. So step#2 would also require downtime? On Jan 14, 12:53 pm, Gints Plivna <gints.pli...(a)gmail.com> wrote: > How about: > 1) CREATE table t1 with the same structure and necessary storage > definitions > 2) INSERT /*+ append */ INTO t1 select only necessary rows > 3) drop old table t > 4) RENAME t1 to t; > > Of course it means, that all privileges should be regranted and > dependant procedural units recompiled. > > Minimum downtime (only steps 3 and 4) - the only problem is need for > extra space and recompile units/regrant privileges. > > Gints Plivnahttp://www.gplivna.eu Thanks
From: Mark D Powell on 14 Jan 2010 09:43 On Jan 14, 8:06 am, UXDBA <unixdb...(a)googlemail.com> wrote: > Thanks Gints . > > But Live table T will have ongoing transaction. > > So step#2 would also require downtime? > > On Jan 14, 12:53 pm, Gints Plivna <gints.pli...(a)gmail.com> wrote: > > > How about: > > 1) CREATE table t1 with the same structure and necessary storage > > definitions > > 2) INSERT /*+ append */ INTO t1 select only necessary rows > > 3) drop old table t > > 4) RENAME t1 to t; > > > Of course it means, that all privileges should be regranted and > > dependant procedural units recompiled. > > > Minimum downtime (only steps 3 and 4) - the only problem is need for > > extra space and recompile units/regrant privileges. > > > Gints Plivnahttp://www.gplivna.eu > > Thanks Yes, data loss is possible with Gints plan if you cannot stop DML activity to the table while the copy is being made. The dbms_redefinition package is your only real option if no downtime can be taken. I would rather get a window and if space is available use the ATLER TABLE MOVE and ALTER INDEX REBUILD commands to handle the reogranization. If free space is not available then you need to use export/truncate/import or drop and re-create in place of truncate since even redefinition requires adequate free space to duplicate the table and indexes exists plus you need space to track the DML activity. HTH -- Mark D Powell --
From: UXDBA on 14 Jan 2010 13:01
On Jan 14, 2:43 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On Jan 14, 8:06 am, UXDBA <unixdb...(a)googlemail.com> wrote: > > > > > > > Thanks Gints . > > > But Live table T will have ongoing transaction. > > > So step#2 would also require downtime? > > > On Jan 14, 12:53 pm, Gints Plivna <gints.pli...(a)gmail.com> wrote: > > > > How about: > > > 1) CREATE table t1 with the same structure and necessary storage > > > definitions > > > 2) INSERT /*+ append */ INTO t1 select only necessary rows > > > 3) drop old table t > > > 4) RENAME t1 to t; > > > > Of course it means, that all privileges should be regranted and > > > dependant procedural units recompiled. > > > > Minimum downtime (only steps 3 and 4) - the only problem is need for > > > extra space and recompile units/regrant privileges. > > > > Gints Plivnahttp://www.gplivna.eu > > > Thanks > > Yes, data loss is possible with Gints plan if you cannot stop DML > activity to the table while the copy is being made. > > The dbms_redefinition package is your only real option if no downtime > can be taken. > > I would rather get a window and if space is available use the ATLER > TABLE MOVE and ALTER INDEX REBUILD commands to handle the > reogranization. If free space is not available then you need to use > export/truncate/import or drop and re-create in place of truncate > since even redefinition requires adequate free space to duplicate the > table and indexes exists plus you need space to track the DML > activity. > > HTH -- Mark D Powell --- Hide quoted text - > > - Show quoted text - Thanks Mark. dbms_redefinition yes I can see in 9.2 http://www.dbspecialists.com/files/presentations/online_redef.html I have checked table "T" and found it suitable for online redefintion. Further, we have plenty of storage space but would prefer to have "no" outage. Mark , why would you prefer alter table move.... if online redifinition is available. do you see we hitting any bug...or this method not safe? Regards |