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

Giganews Newsgroups
Subject: Re: Why does optimizer determine wrong cardinality when using MOD function? test included
Posted by:  poddar0…@gmail.com
Date: 16 Nov 2005

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

Replies

In response to

Why does optimizer determine wrong cardinality when using MOD function? test included posted by peter on 16 Nov 2005