DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: bind variables

  1. #1
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86

    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?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    thanks for the reply...do you have an example of:

    skewed data on a column

    ?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2001
    Location
    Master Control
    Posts
    86
    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
  •  


Click Here to Expand Forum to Full Width