composite key related query

Say there is a composite key on col1,col2,col3,col4 for a table, when I use col2,col3 in an sql query index is not firing and is going for full table scan.(Reason could be col2,col3 may not be driving keys for the index).

In such scenario’s, 开发者_运维知识库whether creating a separate index on col2, col3 is the only solution (or) is there any way to push the existing index to work (say like using hints (or) some other way).

Assuming your statistics are up-to-date, odds are good that your dbms doesn't use the index because it believes a table scan is faster. Since col1 doesn't appear to be included in your query, that makes sense--the index won't help much there.

The simplest way to fix it is to create another index.

create index on your-table-name (col2, col3);

But even that doesn't guarantee your query will use it. On small tables, it's sometimes more efficient to scan the whole (small) table than to scan the index, then read rows from the table.

How a hint might affect your query depends on the dbms. Some don't support hints at all.