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

From: <poddar007_at_gmail.com>
Date: 16 Nov 2005 15:48:43 -0800
Message-ID: <1132184923.585003.69920@g14g2000cwa.googlegroups.com>

poddar007_at_gmail.com wrote:
> 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

SQL> drop index test_ind;

Index dropped.

SQL> create index test_ind on test_t1(mod(user_id,2));

Index created.

SQL> exec
dbms_stats.gather_table_stats('APPS','TEST_T1',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain;
SQL> select * from test_t1 where product_id=80000 and mod(user_id,2)=0;

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=5000 Bytes=1
          35000)

   1    0   TABLE ACCESS (FULL) OF 'TEST_T1' (Cost=21 Card=5000 Bytes=
          135000)

  1* select distinct_keys from user_indexes where index_name='TEST_IND'
SQL> / DISTINCT_KEYS

---

            2

>From 10053 trace:

SINGLE TABLE ACCESS PATH

Column: PRODUCT_ID  Col#: 2      Table: TEST_T1   Alias: TEST_T1
    NDV: 2         NULLS: 0         DENS: 5.0000e-01
    NO HISTOGRAM: #BKT: 1 #VAL: 2
    NDV: 2         NULLS: 0         DENS: 5.0000e-01
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: SYS_NC0000  Col#: 5      Table: TEST_T1   Alias: TEST_T1
    NDV: 2         NULLS: 0         DENS: 5.0000e-01 LO:  0  HI: 1
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: TEST_T1     ORIG CDN: 20000  ROUNDED CDN: 5000  CMPTD CDN:

5000
  Access path: tsc Resc: 20 Resp: 17   Access path: index (equal)

      Index: TEST_IND
  TABLE: TEST_T1
      RSC_CPU: 6376237 RSC_IO: 108
  IX_SEL: 0.0000e+00 TB_SEL: 5.0000e-01   BEST_CST: 21.00 PATH: 2 Degree: 1

Notice Column SYS_NC0000 this is the virtual column i.e function based index
DENSITY is 5.0000e-0.1 (i.e. 1/user_indexes.distinct_keys = 1/2=0.5)

amit Received on Wed Nov 16 2005 - 17:48:43 CST