Home » SQL & PL/SQL » SQL & PL/SQL » sweep to delete rcords from big table
sweep to delete rcords from big table [message #222355] Sat, 03 March 2007 04:39 Go to next message
prakashrao.k
Messages: 13
Registered: September 2006
Location: BANGALORE
Junior Member

Hi all,

I am trying to delete from a big table ...i need to commit for every 10,000 records ...is there any way we could do it in oracle 9i

thnx in advance
prakash
Re: sweep to delete rcords from big table [message #222357 is a reply to message #222355] Sat, 03 March 2007 04:44 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you, instead of DELETE, use TRUNCATE?
Re: sweep to delete rcords from big table [message #222360 is a reply to message #222355] Sat, 03 March 2007 05:09 Go to previous messageGo to next message
prakashrao.k
Messages: 13
Registered: September 2006
Location: BANGALORE
Junior Member

is it possible to do trucate in pl/sql prcoedure...
Re: sweep to delete rcords from big table [message #222361 is a reply to message #222360] Sat, 03 March 2007 05:16 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, using EXECUTE IMMEDIATE. For example:
SQL> create table emp2 as select * From emp;

Table created.

SQL> begin
  2    execute immediate 'truncate table emp2';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp2;

  COUNT(*)
----------
         0

SQL>
Re: sweep to delete rcords from big table [message #222365 is a reply to message #222360] Sat, 03 March 2007 06:10 Go to previous messageGo to next message
prakashrao.k
Messages: 13
Registered: September 2006
Location: BANGALORE
Junior Member

hai littlefoot,

sorry to bother u again! Littlefoot

I will explain then scenario and u tell me the best way.

already I have procedure, that del the records from the backup table when create date is older than 6 months from the sysdate.

issue is raised one table have some 2 crs records in it and I can only del 10000 records before commit and then del 10,000 again

Now how I can handle this in pl/sql procedure with out affecting the performance ...i know this can be done programmatically but it will be huge performance issue ...that is why i am looking for method which will do this ...

I hope this time I am clear than before...pl. help...thnx in advance
Re: sweep to delete rcords from big table [message #222367 is a reply to message #222365] Sat, 03 March 2007 06:17 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
In addition of Littlefoot solution.

How many rows you want to delete.
if very huge then you should create new table with restriction ( according your delete condition) and drop old table.


regards
Taj

[Updated on: Sat, 03 March 2007 06:17]

Report message to a moderator

Re: sweep to delete rcords from big table [message #222372 is a reply to message #222355] Sat, 03 March 2007 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
The act of doing "intermediate" COMMITs will increase the odds that an ORA-01555 error (snapshot too old) will occur.
Re: sweep to delete rcords from big table [message #222374 is a reply to message #222365] Sat, 03 March 2007 08:50 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
committing between deletes will actually be very bad in performance point of view and most possibly induce ora-01555 as Anacedant said.
>>already I have procedure, that del the records from the backup table when create date is older than 6 months from the sysdate.
IMHO,
I would have this 'backup table' to be partitioned so that you can just drop the partition. No need to do anything else.

[Updated on: Sat, 03 March 2007 08:50]

Report message to a moderator

Previous Topic: Problem with Equijoin in SQL
Next Topic: sql query to convert columns into rows
Goto Forum:
  


Current Time: Fri Dec 02 13:51:00 CST 2016

Total time taken to generate the page: 0.08666 seconds