Actually, if you use the compress option (8.1.7) then you want the least
distinct value in front. A test of a few rows is not going to make much
difference in either case. Also you need to make sure your index is being
used.(look at the explain plan). What you want is to minimize the number of
reads.
Jim
"mark" <mark_brehmen_at_yahoo.com> wrote in message
news:fa4781e4.0107302102.453b73ef_at_posting.google.com...
> I would like to know the effect of distinctiveness of index columns on
> index performance.
>
> more specifically is
>
> Create index on table (column1, column2) is different from
> Create index on table (column2, column1)
>
>
> I am new and i read books which state that the more distinct columns
> should be on the left. But i made a small test and the order of the
> multicolumned indexes dont seem to matter. Can anyone explain why???
>
> Create table emp1 as select * from emp;
>
> insert into emp1 select * from emp1
>
> keep doing till you have a few lakh rows.
>
> alter table emp1
> add EMP_rownum integer ;
>
> alter table emp1
> add sex char(1) ;
>
> update emp1 set EMP_rownum = ROWNUM, SEX='M';
>
>
> CREATE INDEX test ON EMP (EMP_ROWNUM,SEX);
>
> Its a stupid to create an index on a column (SEX) with only one
> distinct value. But please read on.
>
> select * from emp1 where emp_rownum =500000 and sex ='M'
>
> Record got in 300 milliseconds
>
>
> drop index test;
>
> --reverse the order of index creation
> CREATE INDEX test ON EMP (SEX,EMP_ROWNUM);
>
> select * from emp1 where emp_rownum =500000 and sex ='M'
>
> ***** Again *** Record got in 300 milliseconds
>
> I would assume the database would behave badly the second query since
> i have created an index with only one distinct value as the leading
> column of the index
>
> Can someone point to me a document which explains how oracle's b*
> variation indexes are stored?
Received on Tue Jul 31 2001 - 00:40:59 CDT
