| Use of Bind variable [message #422373] |
Tue, 15 September 2009 23:16  |
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
|
Junior Member |
|
|
Hello,
After spending sometime on studying what bind variable is, i have got some questions. If I am to use bind variable in a script which simply inserts values to one table from another table, where there are lots of joins(left joins), will it be useful to optimize the execution.
I may have to use the same scripts many times but each time the volume of data is different. Each time the table structure is same but the table name will be different. So I am trying to pass the table names through bind variables. Is it possible?
Lets say at a time I am working with a table of 1gb and the next time I may have to work with 100gb. Will the execution plan be same in both the case since the table structure is same or the execution plan will be different because of the volume of data. I am using oracle 10g.
Your suggestions will be really helpful.
Thanks,
Sameer
|
|
|
|
| Re: Use of Bind variable [message #422410 is a reply to message #422373] |
Wed, 16 September 2009 04:10  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you have large queries that are run infrequently, then there would be little advantage to using bind variables, and you may well get a better execution plan by using hard coded variables.
Bind variables make the most different when you have quick queries that are executed frequently, where the time taken to parse the query is an appreciable percentage of the total execution time
Also, if there is a long time between executions of the query, then the old plan may well have aged out of the pool anyway.
|
|
|
|
| Re: Use of Bind variable [message #422509 is a reply to message #422410] |
Wed, 16 September 2009 23:05 |
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
|
Junior Member |
|
|
Thanks for the quick suggestion, that was really helpful.
Usually the script runs for more than 4 hours when the dataset is really big(more than 50gb). When I tried to calculate the time to generate execution plan, I found it that the execution plan is calculated within few seconds. So in this regard there's no so much gain of time.
Like you said the queries are not run so frequently. So using of bind variable doesn't seem to be appropriate in this case. I will be looking at other options of optimization.
Thanks for your help.
Regards,
Sameer.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
