-
bind variables
when is it a good time NOT to use bind variables. i can find plenty of examples when it is a good time to use them, but does anyone have ideas on when not to use them?
-
When you want the optimizer to adapt the execution plan to take account of skewed data on a column, bind variables are a no-no.
-
thanks for the reply...do you have an example of:
skewed data on a column
?
-
How about a table of employees, where the employees are predominantly male or female? For a query to list all male employees, or all female employees, you'd want different execution plans -- index scan for the minority, table scan for the majority.
-
sorry...i am kind of confused here, would you mind elaborating on how it is bad to use a bind variable here? it would seem that select * from emp where gender=:x would use the same query in library cache...so using 'F' or 'M' would not really matter. are you saying that it would be bad to use the bind variable here if there were 90% records male and only 10% female? if so does that have anything to do with bind variables? thanks again for your thoughts and let me know if my assumptions are correct/incorrect.
-
Queries based on "gender = 'M'" and "gender = 'F'" would optimally be executed in different ways -- one would use an index on gender, the other would not.
If you use bind variables for the query, then it will get parsed and optimized only on the first execution, and that execution plan will be used for every following execution of the SQL regardless of whether 'M' or 'F' is being bound to the query. Therefore only one of them will be optimized correctly.
-
ok, so i guess the rule gets back to what you originally said...if you want the optimization for a skewed column then don't use bind variables. so would it follow that having a bunch of different random values (evenly distributed) in a column would make good use of bind variables, where as large groups of the similar values (with a small delta between the groups) would not? i was mainly looking at the advantage for memory with the shared pool.
Last edited by tron; 07-11-2003 at 05:50 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|