If i create a single composite index on [LKUP_ID] and [ATTR_VAL] columns, will the index be useful if I run a query like this:
SELECT LKUP_ID, ATTR_VAL
My question is, will the index be useful only if I use both LKUP_ID and ATTR_VAL in the WHERE clause or can I use either one of these columns in the WHERE clause.[/quote]
The index will be ued if you filter on either LKUP_ID or LKUP_ID and ATTR_VAL. Think of a phone book and the way it stores and sorts information, and imagine LKUP_ID as last name and ATTR_VAL as first name. If you search for Jones and then look for Andrew the sort order in the book helps, but you can't use it to find Andrew and then filter to Jones.