Q
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?

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

 SELECT MyCol
   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


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close