Home » SQL & PL/SQL » SQL & PL/SQL » Multiple insert statement or One dynamic insert
Multiple insert statement or One dynamic insert [message #400602] Wed, 29 April 2009 01:36 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Gurus,

Have look at following facts
SQL>select * from v$version;

BANNER
================================================================================
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

SQL>desc approved_links;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APPROVAL_PK                               NOT NULL NUMBER
 MASTER_LINK_FK                                     NUMBER
 CHILD_LINK_FK                                      NUMBER
 ENGINE_FK                                 NOT NULL NUMBER(3)
 RANK                                               NUMBER
 CREATED_BY_FK                             NOT NULL NUMBER
 CREATED_ON                                NOT NULL TIMESTAMP(6)
 LAST_LOGIN_FK                             NOT NULL NUMBER
 LAST_MODIFIED                             NOT NULL TIMESTAMP(6)
 LAST_MC                                   NOT NULL VARCHAR2(50)
 LAST_IP                                   NOT NULL VARCHAR2(20)


Now I have to insert more than three rows at a time in this table.
What approach if insertion should I follow -
1>multiple insert statement one by one or
2>One dynamic 'insert all' statement
?
Please suggest me the optimized way.

regards,
Delna
Re: Multiple insert statement or One dynamic insert [message #400605 is a reply to message #400602] Wed, 29 April 2009 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why your solution 2 mentionned "dynamic"?
Explain IN DETAILS what is your case.

Regards
Michel
Re: Multiple insert statement or One dynamic insert [message #400647 is a reply to message #400605] Wed, 29 April 2009 03:49 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Actually how many insert statement are fired is not fixed. It depends on user interaction with front-end.
So that I can fire N number of insert statement seperatly or make one string for INSERT ALL statement and use that insert string with EXECUTE IMMEDIATE statement.
But I don't know which one is optimized way.
Hope this is understandable.

regards,
Delna
Re: Multiple insert statement or One dynamic insert [message #400652 is a reply to message #400647] Wed, 29 April 2009 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Avoid Dynamic SQL when you don't need to use it. It's more resource intensive, and harder to debug.
Re: Multiple insert statement or One dynamic insert [message #400655 is a reply to message #400652] Wed, 29 April 2009 04:05 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you JRowbottom sir for quick reply.

But is there any benchmark on which we can decide so that we can go with multiple static statement or one dynamic statement?

regards,
Delna
Re: Multiple insert statement or One dynamic insert [message #400665 is a reply to message #400655] Wed, 29 April 2009 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
But is there any benchmark on which we can decide so that we can go with multiple static statement or one dynamic statement?

I'm pretty sure you can do it by yourself.
Maybe you should work for us a little bit, do it and post the result.

Regards
Michel
Re: Multiple insert statement or One dynamic insert [message #402394 is a reply to message #400665] Sat, 09 May 2009 16:37 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
maybe while you are doing your own research and testing, you could also look into bulk insert? I presume (maybe incorrectly) that you want to execute the same basic insert multiple times just with different values.

http://www.dba-oracle.com/oracle_tips_rittman_bulk%20binds_FORALL.htm

There may be better links that this one, but these guys are easy to find.

Good luck, Kevin
Previous Topic: Oracle rows displays not in same order
Next Topic: unable to reduce the IO Cost of the query
Goto Forum:
  


Current Time: Tue Dec 03 13:40:54 CST 2024