Re: INSERT RETURNING INTO Clause

A copy of this was sent to Bill Mann <bill.mann_at_worldtalk.com> (if that email address didn't require changing) On Mon, 21 Jun 1999 22:14:30 -0700, you wrote:

>Thanks,
>I now understand that this should work in Oracle 8.
>
>BTW is there any performance reasons you recommended using Bind Variable instead
>%s. I created my statement with a sprintf.
>

big time difference. huge difference. perhaps 90% of your execution time in the database can be spent parsing insert statements and only 10% actually executing it. You were doing inserts like this:

> > INSERT INTO %s VALUES (
> > ids.nextval, '%s', %s, '%s',
> > %s, %s, fs.currval||'%s', %s, NULL, NULL)
> > RETURNING imkey INTO ?;

Lets say the table was T. You might create insert statements like:

insert into t values ( ids.nextval, 'a', 1, 'b', 2, 3, fs.currval||'c', 'd', NULL, NULL );
insert into t values ( ids.nextval, 'a', 2, 'b', 2, 3, fs.currval||'c', 'd', NULL, NULL );
insert into t values ( ids.nextval, 'a', 3, 'b', 2, 3, fs.currval||'c', 'd', NULL, NULL ); each of those, even though similar, are different and must do the following:

• look in the shared sql area for a compiled statement that matches this (this always fails since you always make a unique statement)
• parse it
• compile it
• optimize it
• security check it
• execute it

If, on the other hand, you submited a statement like:

insert into t values ( ids.nextval, ?, ?, ?, ?, ?, fs.currval||?, ?, NULL, NULL )

three times with three different sets of bind variables what would happen is:

• on the first submit, if that statement HAD NOT BEEN submitted by some other session while the database was up we would:
  • look in the shared sql area for a compiled statement that matches this (this always fails right after the instance is started but succeeds after this first run)
    • parse
    • compile
    • optimize
    • security check
    • exeucte
• on the second and subsequent submits, we would
  • find the statement in sql area
  • execute it

You cannot use bind variables for identifiers like table names (hence you must leave the %s for the "insert into %s" part) but every where you can use them -- you should

>Thanks,
>Bill
>
>Thomas Kyte wrote:
>
>> A copy of this was sent to Bill Mann <bill.mann_at_worldtalk.com>
>> (if that email address didn't require changing)
>> On Mon, 21 Jun 1999 00:19:44 -0700, you wrote:
>>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jun 22 1999 - 08:25:27 CDT