Re: Why does optimizer determine wrong cardinality when using MOD function? test included

You have 10000 rows in the table.
You have two distinct values for product id (so density = 0.5 for product_id)

For query
select * from test_t1 where product_id=800000

selectivity= 1/num_distinct (product_ids) = 1/2 = 0.5 computed cardinality = 10000*0.5=5000

For query
select * from test_t1 where product_id=80000 and mod(user_id,2)=0

for product_id=80000 selectivity=0.5 (as above) for mod(user_id,2) Since you are applying a function to a database column oracle assumes that this predicate will only return 1% of the rows
therefore the selectivity is 0.01

So combined selectiviy = 0.5 * 0.01= 0.005 Cardinality = 10000*0.005=50

This is a general problem which happens when you apply a function to a database column.

To solve this problem you should create a function based index on mod(user_id,2) So oracle will use this index to calcualate the effective selectivity of the column.

amit Received on Wed Nov 16 2005 - 16:49:03 CST