Why does the optimizer use an index when the select statement looks like this:select customer_num from Schema.Table where not customer_num = 1 and not customer_num = 2;but doesn't use the index when the select statement looks like this:select * from Schema.Table where not customer_num = 1 and not customer_num = 2;The only difference between the two queries is the fact that the second query returns all of the columns.I have verified this behavior using the Query Plan feature.The output from the first query looks like this:10 1 1 SCAN INDEXONLY SCANThe output from the second query look like this:11 1 1 SCAN TABLE SCAN
This will happen when customer_num is a key. The reason for this that when only this column is in the select list, PointBase can use the index to get the value to return in the result set, so the cost is less than a table scan. In the other query, because all columns are being returned, a table scan is computed by the optimizer to be cheaper. The problem is that if the statement used a predicate such as '...customer_num=1 and customer_num=2' it would be considered a range predicate and would use an index if available and the optimizer computed it as the best plan. However with heap table organization in PointBase '...NOT customer_num=1 and NOT customer_num=2' can't utilize an index. Similarly, we have optimized '...IN (1,2)' to use an index if available, but have no optimization yet for a atatement with a predicate like '...NOT IN (1,2)'. One possible solution for this would be to change the table organization to index organized. This requires that you have a primary key defined on the table, and that the table not have lob columns that are larger than the page size, as these aren't supported in our organization index implementation. If your table is defined as index organized, you query would get an index scan with these queries as written.
Rate this page:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.