Table scan vs Index scan on very similar query

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.