| need some help with PL/SQL [message #9557] |
Wed, 19 November 2003 08:19  |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
I'm trying to write this PL/SQL code that will go through the records of a table and delete the records with a certain condition...also doing a save after every 100. When I run this code below it works but crashes my sql+ which is a cause for concern for me...I guess the way I have the code below must be filling up some sort of buffer...and my committ for every 100 must not be helping..cna anyone offer any advice for help on how I can prevent the sql+ from crashing..
I thought this would have been right :
Declare
v_id varchar2(8);
v_count number(8);
cursor v_cursor is
select name_id from record_table;
begin
v_count := 0;
open v_cursor;
loop
fetch v_cursor into v_id;
exit when v_cursor%notfound;
delete from record_table
where userid = 'admin3';
v_count := v_count + 1;
if v_count = 100 then
commit;
v_count := 0;
end if;
end loop;
close v_cursor;
commit;
end;
/
|
|
|
|
| Re: need some help with PL/SQL [message #9558 is a reply to message #9557] |
Wed, 19 November 2003 08:34  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Andy,
No need for PL/SQL here, just use straight SQL:DELETE FROM record_table WHERE userid = 'admin3';
COMMIT;
For example, here are some drums he commonly beats that directly relate to your question:<ol>[*]The Tom Kyte mantra:
If you can do it in a single SQL statement -- do it.
If you cannot, do it in PL/SQL.
If you cannot, use a little Java stored procedure.
If that doesn't cut it, use a C external procedure.[*]COMMITing within a cursor loop (a no-no)</ol>Happy learning,
Art.
|
|
|
|
| Hi Art [message #9559 is a reply to message #9558] |
Wed, 19 November 2003 08:45 |
andy
Messages: 92
Registered: December 1999
|
Member |
|
|
I wanted to do this with a single SQL statement originally but have been requested from Q/A that I am to use a Pl/SQL because there are over 1 million records in that table and to ensure that I am saving every 100 that get deleted in case there is a hiccup on the server.....
Can you help me with my PL/SQL code?
Thanks for yoru response
|
|
|
|
| Re: Hi Art [message #9560 is a reply to message #9559] |
Wed, 19 November 2003 09:09 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Committing within a PL/SQL cursor loop is the wrong approach to this problem. If there's a "hiccup on the server", just re-issue the command. You're doing a DELETE on one table, what have you lost? And what does the PL/SQL approach buy you?
What error does your PL/SQL version crash with?
The DELETE will run so much faster in straight SQL. Can you benchmark both scenarios for your QA department in a test environment?
A.
|
|
|
|
| Re: Hi Art [message #9561 is a reply to message #9560] |
Wed, 19 November 2003 09:16 |
andy
Messages: 92
Registered: December 1999
|
Member |
|
|
No errors show up it is just crashing...the sql+ program although when I look up the table after restarting sql+ I can see that my code works. I figure it has soemthing to do with the buffer.
Approaching the Q/A person (just a person not a department) can be a bit of a challenge...he is quite persistant on how he wants thinsg done...
I fully agree with you and originally was the way that I wanted to approach the task...
However my SQL liner was shot done and was told I was specifically told to save every 100 records...
I'm not sure I can set up a benchmark scenario but more then welcome any advice on your part....
I hate to say it but I'm not sure if i can convince the Q/A person even with proof...he is quite headstrong and hard to approach if he wants to be right even if he isn't ...(I'm sure we all have those around us don't we) ;-)
|
|
|
|
| Re: Hi Art [message #9562 is a reply to message #9561] |
Wed, 19 November 2003 09:20 |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
|
just curious but are there ways I cna get around this in pl/sql without a loop...therefore avoiding the commit in the loop?
|
|
|
|
| Re: Mass delete [message #9563 is a reply to message #9562] |
Wed, 19 November 2003 10:55 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Outside of issues introducted by triggers or referential integrity, the only error you could have (in normal operation) would be that you have run out of rollback space for this operation.
The proper way to deal with this is to size your rollback segments properly for the types of transactions you do and issue the single delete as Art mentioned. If you need to delete 1M rows, and you do, the DBA needs to make your RBS bigger. RBS is not a scarce resource. If it is not sized properly, developers waste time writing workarounds that take longer to execute and consume more resources.
If you have no control over the RBS sizing, then here is an approach that avoids fetching across commits, but still takes longer than a single delete.
begin
loop
delete from record_table
where userid = 'admin3'
and rownum <= 50000; -- make this as large as possible
exit when sql%rowcount = 0;
commit;
end loop;
end; |
|
|
|
|
|
| ok so im lost again [message #9565 is a reply to message #9564] |
Wed, 19 November 2003 12:04 |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
Talked to Q/A guy and was given a talk about how the commits are needed in the loops becuase of the sensitive information involved....he informed me that I do in fact need to use rownum and rowtype and save every 100 row num's....does this mean I shouldn't use my counter at all...I tried a few ideas with the latest one below but I think im way off...anyone have any advice...sorry about that
Declare
v_rownum number(10);
v_id varchar2(8);
v_count number(8);
cursor v_cursor is
select rownum,name_id from record_table
where userid = 'admin3';
begin
v_count := 0;
open v_cursor;
loop
fetch v_cursor into v_rownnum, v_id;
exit when v_cursor%notfound;
delete from record_table
here rownum = v_rownum;
v_count := v_count + 1;
if v_count = 100 then
commit;
v_count := 0;
end if;
end loop;
close v_cursor;
commit;
end;
/
|
|
|
|
|
|
| Re: ok so im lost again [message #9567 is a reply to message #9566] |
Wed, 19 November 2003 13:28 |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
thanks Todd
no comment on my part in case he reads these boards (gulp) haha in which case im probably in huge trouble as it is ;-)
|
|
|
|
|
|
| Re: ok so im lost again [message #9571 is a reply to message #9567] |
Wed, 19 November 2003 15:13 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
You do not have to worry about it. People like him never read these boards.
P/S
Sorry, after the word "read" I had to place a period.
|
|
|
|
| solved [message #9580 is a reply to message #9571] |
Fri, 21 November 2003 06:56 |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
I finally got a workable solution using an example with
if (mod(c1%rowcount,1000)=0) then commit;
it worked well and all is well in the world :-)
back to my learning PL/SQL
so much to learn so little time..
thanks for the reference to ask tom Art..I am findingthe site very useful.
|
|
|
|
| Re: solved [message #9585 is a reply to message #9580] |
Fri, 21 November 2003 10:36 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Andy:
Just for the record, you are fetching across a commit which is exactly what Art and I have recommended avoiding in our examples. You have opened up yourself to the possibility of ORA-01555 errors.
It is so frustrating to spend so much time answering questions and pointing people in the right direction, and they end up doing exactly what you recommend not to do...
|
|
|
|
| Re: solved [message #9588 is a reply to message #9585] |
Fri, 21 November 2003 11:28 |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
My apologies to you Todd and to Art if I offended at all. I certainly didn't mean to. And I have to say that I did learn by both your posts....I do appreciate your help very much.
I was basically given instructions on how I was to proceed and it was reallymy only option...
until I prove myself to be a stronger developer (which I'm working on) I'm afraid im behind the 8 ball in proving anyone in my work enviorment differently.
I know this won't help ....
I wrote my code and tested it in development under every single scenario very carefully I could have thought of.
It had worked and did not cause any ORA-01555 error.
The code was approved by Q/A....
I can only say that I am very sorry for causing you frustration. :-(
This is all new to me...I'm hoping to get better
|
|
|
|
| Re: solved [message #9589 is a reply to message #9580] |
Fri, 21 November 2003 11:34 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
You went back to where you started !
Apart from the Fetch across commit issue, you will need to understand the cost of a commit.
Everytime you commit , following things happen in the background :
i) the transaction table of the rollback segment needs to get updated with commit scn
ii) the rollback segment header needs to be updated with the information that its free now
iii) these rollback segment changes need to written to the online redolog files
iv) LGWR has to flush the redo entries from the redolog buffer cache into the redolog files
v) Commit confirmation is returned to the calling program.
And now you start another transaction , which undergoes additional overhead in allocating entries in rollback segments etc etc..do the processing and there comes another commit ,so soon ! It slows down the processing so much.
Size your rollback segments approprietly and do the DML in ONE SIMPLE SQL , whenever possible. You dont have to use PL/SQL just for the sake of using it.
-Thiru
|
|
|
|
| Re: solved [message #9591 is a reply to message #9589] |
Fri, 21 November 2003 13:33 |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
|
I have this printed and placed on the wall of my cubicle...thanks for the info
|
|
|
|
| Re: no worries [message #9593 is a reply to message #9588] |
Fri, 21 November 2003 15:07 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Andy, no offense taken at all. I realize that sometimes there are constraints placed upon you that you do not have control over. As you continue to develop your skills, you will have more and more control over how things are done.
PL/SQL is a great language for data manipulation, but I only use the procedural elements of it (loops, etc.) when I cannot accomplish the task in a single SQL statement (or set of statements).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
