multiple indexes on the same column

[Tiffany Thang <tiffanythang@xxxxxxxxx>]

Hi,

Can someone explain the use of creating multiple indexes on the same column? 

How would the optimizer determine which index to use? From my brief testing, the optimizer picked the latest created index, testidx3. Can you provide a scenario where creating multiple indexes on the same column would be beneficial? 

create table test (a int, b int);

create index testidx1 on test (a);

create index testidx2 on test (a);

create index testidx3 on test (a);

Thanks.

Tiff