- You are here
- Comp Newsgroups Archived.At
- comp.databases.oracle.server
- 2005 November
- Re: Why does optimizer determine wrong cardinality when using MOD function? test included

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

- Re: Why does optimizer determine wrong cardinality when using MOD function? test included posted by poddar0…@gmail.com on 16 Nov 2005

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