RE: pl/sql help
- From: "Kenneth Naim" <kennaim@xxxxxxxxx>
- To: "'Jack van Zanen'" <jack@xxxxxxxxxxxx>
- Date: Thu, 30 Jul 2009 23:36:02 -0400
Good Point. I would still disable/rebuild indexes.
Ken
From: jack.van.zanen@xxxxxxxxx [mailto:jack.van.zanen@xxxxxxxxx] On Behalf
Of Jack van Zanen
Sent: Thursday, July 30, 2009 8:01 PM
To: kennaim@xxxxxxxxx
Cc: JSweetser@xxxxxxxx; Oracle-L Group
Subject: Re: pl/sql help
as a variant on step 3
create table with records you want to keep (ctas)
truncate the original
insert append the records back to original
Minimal undo as well and no need to re-create triggers, indexes etc etc
downside => downtime
Jack
2009/7/31 Kenneth Naim <kennaim@xxxxxxxxx>
There are a few issues with your program.
1. the in list variable cannot be list of values, it should be one number
and only a one number. You can multiple variables (up to 1000), for all
clause, or dynamic sql but all these methods are over kill development wise
and are slower and use more undo over all so I won't go into them.
2. The developer had the right idea as a single statement will be faster
than a loop but will use a lot more undo space. I would size the undo
appropriate to the statement and if you want to reclaim the space later just
create a new undo tablespace and swap them, and drop the big one.
3. Depending on how many rows we are talking about, it will most likely be
much faster to create a new table as select the rows you want to keep, then
add the appropriate constraints, triggers, indexes, statistics etc. on it.
4. If you don't want to go through step recommendation 3 then you should
atleast drop the indexes on the table, then do the delete, then create the
indexes. It will use 80+% less than undo and will be much faster; especially
if you build the indexes in parallel/ nologging but remember to alter them
logging noparallel (or whatever their parallel/logging state was from before
dropping them).
Ken
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sweetser, Joe
Sent: Thursday, July 30, 2009 6:48 PM
To: Oracle-L Group
Subject: pl/sql help
Greetings gurus,
10.2.0.2
RH Linux AS4
I am trying to write a pl/sql program to delete a bunch of records.
This came up because a developer wrote a sql statement to delete
everything but it was running out of undo space. I thought it would be
easy to throw into a pl/sql program and commit along the way to avoid
that issue.
The gist of it is that I am building a list of values to use in the
delete statement and passing it in as a bind variable but am getting an
error when executing it. The SQL statement is "delete from table where
column in (num1, num2, num3, num4,...num50);" I am using a varchar to
build the num1, num2, num3... list and then trying to pass the whole
thing to sql statement on the execute immediate. When I do, I get an
invalid number error. I think I am missing something obvious but can't
figure it out.
Here's the output from when I execute it. I added the echo'ing of the
sql statement and the list_of_values while trying to debug. I have
tried putting the ()'s in the list_of_values but get the same error.
SQL> exec epicenter_delete.whack_it_all
delete from pol_predetail where ParentObjID in (:1)
10110,10111,10112,10113,10114,10115,10116,10117,10118,10119,10120,10121,
10122,10123,10124,10125,10126,10145,10146,10147,10148,10149,10150,10151,
10152,10153,10154,10155,10156,10157,10158,10159,10160,10161,10162,10163,
10164,10165,10166,10167,10168,10169,10170,10171,10190,10191,10192,10193,
10194,10195
BEGIN epicenter_delete.whack_it_all; END;
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "JOE.EPICENTER_DELETE", line 194
ORA-06512: at line 1
Elapsed: 00:00:00.92
SQL>
The program goes something like this:
PROCEDURE whack_it_all IS
SQL_Stmt VARCHAR2(512); -- holds sql stmt to executed
list_of_values VARCHAR2(512); -- holds id's to be deleted
<snip>
CURSOR c1 IS
SELECT ParentObjID
FROM blah, blah, blah;
<snip>
BEGIN
/* Initialize counters/variables */
tmp2_count := 0;
list_of_values := '';
FOR premium_detail_rec IN c1
LOOP
list_of_values := list_of_values || premium_detail_rec.ParentObjID;
tmp2_count := tmp2_count+1;
IF tmp2_count = 50
THEN
SQL_Stmt := 'delete from pol_predetail where ParentObjID in
(:1)';
dbms_output.put_line (SQL_Stmt);
dbms_output.put_line (list_of_values);
EXECUTE IMMEDIATE SQL_Stmt using list_of_values; <----- this
is line 194
list_of_values := '';
tmp2_count := 0;
ELSE
list_of_values := list_of_values || ',';
END IF;
END LOOP;
Any/all ideas/help appreciated.
-joe
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
Jack van Zanen
-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation
Other related posts:
