First page Back Continue Last page Graphics

Continuation of Notes


Notes:

Notes continued from the previous page…
Skewed data. Imagine that you have 500,000 rows that meets one criteria (e.g. WHERE STATE = ‘CALIFORNIA’) and just 1,000 rows that meet a different criteria (WHERE STATE = ‘DELAWARE’). As we learned in the “Getting to Know CBO” lesson, if this is an indexed column you’ll want to create histograms in this situation. We also learned that Oracle peeks at the contents of the bind variable at hard parse time. If the bind variable in the 1st query (hence hard parse) contains ‘CALIFORNIA’, CBO will choose a full scan. Subsequent executions will use the same execution plan (full scan) even if the bind variable contains ‘DELAWARE’, thus delivering sub-standard performance to return just 1000 rows. If this describes your situation, you should use a constant in this query, not a bind variable.