Multiple indexes
Here is my problem definition:
I have a table CUSTFILE with the following fields:
custno number(10)
firstname char(30) not null
lastname char(30) not null
login_name char(8) not null
I have the following Indexes for CUSTFILE:
Index_1 unique (custno)
Index_2 non-unique (lastname)
Index_3 non_unique (firtname)
Index_4 unique (login_name)
All others being equal, I have the following select statements:
Item ONE
Select * from custfile
where custno = 1234
and login_name = 'jeff'
Item TWO
Select * from custfile
where firstname = 'John'
and lastname = 'Smith'
Item Three
Select * from custfile
where custno > 100
and lastname = 'Smith'
In each scenario which index will be used?
Is it possible to provide a HINT to the SQL query to use a specific index?
Thanks in advance.
Jeff Cerrafon
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
jeff.cerrafon@bellsouth.co.nz (email address)
+64-9-357-5158 (Direct Line) +64-21-685158 (Mobile Phone)
+64-9-357-1697 (Fax) +64-21-685157 (Mobile Fax)
My Homepage => http://iconz.co.nz/~cerrafon <=
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~