| execute immediate with bind variables [message #148491] |
Thu, 24 November 2005 08:18  |
vgs2005
Messages: 123
Registered: April 2005
|
Senior Member |
|
|
hi,
I have to optimize a PL/SQL code, it has lots of INSERT operations on same table wherein is a constant change on 4 column values to insert. Example:
It goes like:
INSERT INTO TABA VALUES (a, b, c, '1', '2', '3', '4');
...
INSERT INTO TABA VALUES (a, b, c, '3', '8', '7', '5');
..
INSERT INTO TABA VALUES (a, b, c, '5', '5', '8', '44');
..
Will the performance improve if I replace them with:
EXECUTE IMMEDIATE
('INSERT INTO TABA VALUES (a, b, c, :b1, :b2, :b3, :b4)')
USING ..
|
|
|
|
|
|
| Re: execute immediate with bind variables [message #149018 is a reply to message #148491] |
Tue, 29 November 2005 12:54  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Wait...no, if I'm reading the OP correctly then it won't help at all, won't matter; if anything hurt, by using dynamic sql when static would do.
Bind variables only come into play when using dynamic sql, or sql statements passed in as a string of text for instance from a java program. If the sql statements exist already inside plsql code as static statements, then oracle takes care of the binding and the sharing for you, no need to use bind variables.
(of course the best thing to do is to test it both ways and check the parsing, maybe someone can do that for us or point to where it has been done before. Or maybe I can after my meeting).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
