How index on column b will be useful when it is not in WHERE?
A reader, July 29, 2005 - 4:03 am UTC
insert into a values(&a,&b);
-- Created both indexes.
select * from a;
A B
---------- ----------
100 1
200 1
300 3
300 5
600 6
100 8
6 rows selected.
interspc@spex.NNVH023A> select distinct(b) from a where a = 100 ;
B
----------
1
8
2 rows selected.
Auto trace result shows..
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'A'
3 2 INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE)
Could you please explan how index on b is useful get to distinct(b)?
Thanks
July 29, 2005 - 8:44 am UTC
well, we have to guess since you don't provide the entire example.
idx_2 is on A.
idx_2 is used to find rowids of rows such that a=100
this rowid we find is in turn used to get the row (table access by index rowid)
we get the row which has B on it (and such that a=100, we know that from the index)
and distinct it.
How can we see the use of idx_1 ?
A reader, July 29, 2005 - 9:43 am UTC
I created both the indexes as in the original Question. Sorry for not mentioning that.
ie, create index idx_1 on a (a,b)
and create index idx_2 on a (a)
Will it be possible to see the use of index on column b(Here index idx_1) in getting DISTINCT(b) values ? Just curious to know that.
As always, Thanks.
July 29, 2005 - 12:30 pm UTC
yes, use the cbo and you'll be happier.
when both idx_1 and idx_1are there...
A reader, July 29, 2005 - 9:59 am UTC
Why can't oracle use idx_1 since that would be the fastest?
July 29, 2005 - 12:31 pm UTC
use the cbo....