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
- Dozens more answers to tough Oracle questions from Karen Morton are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.