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) > > > > 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 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ set explain on prior to running your query will generate a file showingyou which path the optimizer is using. The following will be in your onconfig file .... # OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. OPTCOMPIND 0 # To hint the optimizer -- Jorge Torralba Intel Information Technology HF2-71 (503)696-4587 5200 NE Elum Yung Parkway Hillsboro, Or 97124 ===================================================== Any views or opinions expressed by me do not reflect those of Intel Corp.