Problem solve Get help with specific problems with your technologies, process and projects.

Histograms and bind variables

Is true that "histograms" can only be used by the optimizer when in an SQL statement I use literal variables, and...

not when I use bind variables?

  FORM MyTab1
  WHERE Key = 'xx';
Do not use Histogram?

   FORM MyTab
  WHERE Key = :b;
Use Histogram?

It is true that histograms will only be used if the select statement does NOT use bind variables. Recall that histograms are created to describe the distribution of data values for a particular column. Bind variables are bound at run time. Therefore, when the optimizer is determining the execution plan for a statement with a bind variable, it does not have a specific value which it can use with a histogram. For instance, if you had a histogram on a column that contained color values (RED, WHITE, BLUE). If you use a bind variable in the where clause (WHERE color = :the_color) the optimizer can't use the histogram because it doesn't have an actual value to compare. So, it will ignore the histogram and makes it execution plan choices based on other factors.

So in your example select statements, the first one will use a histogram (if available) and the second one will not.

For More Information

This was last published in June 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.