469,088 Members | 1,268 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

loss of performance when using anonymous pl/sql blocks?!

Instead of using a set of different sql-statements i have decided to use
an single, anonymous pl/sql block with host-variables. For example:

DECLARE
cmp NUMBER := :hostvariable;
BEGIN
UPDATE t_sample SET c_id=0 WHERE tid>cmp;
END;

Are there any disadvantages concerning simle update-statement?
Won't oracle cache such blocks, do they need more processing time or
more cpu-power on the server? Or does oracle treat them as any other
sql statement?

thx, markus
Jul 19 '05 #1
2 3373

"Markus Breuer" <ma***********@gmx.de> wrote in message
news:3F**************@gmx.de...
Instead of using a set of different sql-statements i have decided to use
an single, anonymous pl/sql block with host-variables. For example:

DECLARE
cmp NUMBER := :hostvariable;
BEGIN
UPDATE t_sample SET c_id=0 WHERE tid>cmp;
END;

Are there any disadvantages concerning simle update-statement?
Won't oracle cache such blocks, do they need more processing time or
more cpu-power on the server? Or does oracle treat them as any other
sql statement?

thx, markus

The fastest way would be a straight insert with a host variable, keep the
cursor around, rebind and reexecute. If you were inserting a lot of rows at
a time then use the array interface to blast them in 100 or more rows at a
time.
Jim
Jul 19 '05 #2
Markus Breuer <ma***********@gmx.de> wrote in message news:<3F**************@gmx.de>...
Instead of using a set of different sql-statements i have decided to use
an single, anonymous pl/sql block with host-variables. For example:

DECLARE
cmp NUMBER := :hostvariable;
BEGIN
UPDATE t_sample SET c_id=0 WHERE tid>cmp;
END;

Are there any disadvantages concerning simle update-statement?
Won't oracle cache such blocks, do they need more processing time or
more cpu-power on the server? Or does oracle treat them as any other
sql statement?

thx, markus

why dont you run it and find out? Oracle will still cache it. This
shouldnt have any effect on performance.
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by Doug Holton | last post: by
6 posts views Thread by Erik Cruz | last post: by
4 posts views Thread by zzfreddybb | last post: by
9 posts views Thread by Java script Dude | last post: by
6 posts views Thread by RepStat | last post: by
3 posts views Thread by Martin B | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.