I have a problem where I'm getting a significant variance in response time when running a SQL select statement over a view. The view is a join of 12 files where all of the joins are left outer. All of the selection criteria use fields from the primary file, and I have an EVI over the primary file that contains only the fields used for the selection clause. When I execute two select statements where the only difference between the two is the values of the fields in the selection clause, however, one statement consistently uses the access path created by the EVI in its execution plan, while the other one always does a table scan on the primary file. What is causing this?
The difference in behavior is due to the selection values causing the cost-based query optimizer to react differently to the similar select statements. Cost-based optimizers look at the selection field values passed in and make a determination on how many rows are going to be returned based on the selection values passed in. For example, the optimizer might determine that a selection value of 'A' will return 1000 rows and a selection value of 'Z' will return 1,000,000 rows - for the 'A' selection value the optimizer might choose an index scan (i.e., use the EVI) and use a table scan for the selection value of 'Z' since it's determined a large majority of rows in the able will be returned by 'Z'.
MORE INFORMATION ON THIS TOPIC
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: tips, tutorials and more.
Check out this Search400.com Featured Topic: Database issues resolved
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading