Re: Multiple indexes
Hi,
The only way to REALLY know what index gets selected is to use
SET EXPLAIN and examine the query plan output. The best wayto "HINT" the engine is to UPDATE STATISTICS on a regular basis.
SET EXPLAIN can be turned on in you program or in dbaccess - itgenerates a file named sqexplain.out in the current directory
(if you are logged into the server) or your home directory on
the server if you are running from a client machine.
UPDATE STATISTICS can be run at various level of detail (for
engine versions 7 or greater)
Have fun,
Bill
}
} 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 <=
} ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
}
--
Bill Ennis Voice: 312-474-7516
SSA Fax: 312-474-7460
500 W. Madison email: ennis@ssax.com ennis@accesschicago.net