Re: Multiple indexes

Jeff Cerrafon wrote: > > 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 > -- Hi, I hope there is no need to hint the optimizer to use a specific index. I agree with Bill Ennis words. Using OnLine DS 7.x there is an additional HINT for the optimizer, it's called OPTCOMPIND. If you would set this environment variable to 0, the optimizer will use one of your indexes, even if it makes no sense. If you would set this environment variable to 2 ( default since 7.10UD1 ), the optimizer will make a cost based decision. In this case sequential scans are preferred when it's less I/O intensive. Have a nice day bye Stefan PS: There is a way to hint the optimizer, to use a specific index. But everyone should avoid these rules unless they got real problems.