index distinctiveness question.

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:02:45 CDT