Re: unique key index selectivity
- From: amonte <ax.mount@xxxxxxxxx>
- To: "Martin Berger" <martin.a.berger@xxxxxxxxx>
- Date: Sun, 14 Dec 2008 21:21:50 +0100
Hi
I think I was using a really old formula from ML, it basically says that
index selectivity is distinct keys/num_rows so i was expecting 1 :-S
I forgot the formula which I should use is 1/distinct keys!
Thanks for the help
Alex
On 12/14/08, Martin Berger <martin.a.berger@xxxxxxxxx> wrote:
>
> Hi Alex,
>
> as you have 14 rows with 14 distinct keys and you are selecting only exact
> 1 row, your selectivity is 1/14 => 0,071429
> The numbers you will get is the cardinality, which is 1 in your example.
>
>
> To get a selectivity of 1, you should run something like
> select empno from emp;
> (With a cardinality of 14, in this case).
>
>
>
>
> hth,
> Martin
>
> --
> Martin Berger http://berxblog.blogspot.com/
>
> Am 14.12.2008 um 20:08 schrieb amonte:
>
> Hi
>
> I am checking an index selectivity using 10053 trace but while I was
> checking for something else I noticed that index selectivity of a unique
> index is not 1 as expected?
>
> For example testing with EMP table and empno.
>
>
> SQL> create unique index emp_i1 on emp(empno);
>
> Index created.
>
> SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP', estimate_percent =>
> 100, cascade => true,-
> > method_opt => 'for all columns size 1')
>
>
> SQL> alter session set events '10053 trace name context forever, level 1';
>
> Session altered.
>
> SQL> select mgr from emp where empno = 7369;
>
> MGR
> ----------
> 7902
>
> SQL> select table_name, index_name,
> 2 distinct_keys, num_rows from user_indexes
> 3 where index_name = 'EMP_I2' or index_name = 'EMP_I1';
>
> TABLE_NAME INDEX_NAME
> DISTINCT_KEYS NUM_ROWS
> ------------------------------ ------------------------------ -------------
> ----------
> EMP EMP_I1
> 14 14
> EMP EMP_I2
> 3 14
>
>
> Now when check 10053 trace file I see this:
>
> Access Path: index (UniqueScan)
> Index: EMP_I1
> resc_io: 1.00 resc_cpu: 8381
> *ix_sel: 0.071429 ix_sel_with_filters: 0.071429
> * Cost: 1.00 Resp: 1.00 Degree: 1
> Access Path: index (AllEqUnique)
> Index: EMP_I1
> resc_io: 1.00 resc_cpu: 8381
> * ix_sel: 0.071429 ix_sel_with_filters: 0.071429
> * Cost: 1.00 Resp: 1.00 Degree: 1
> Shouldnt ix_sel be 1?
>
>
> This is 10.2.0.3
>
> TIA
>
> Alex
>
>
>
>
>
>
>
>
>
>
Other related posts:
