Modify a sql query to use one of the existing index

sureshreddy24 · New User Joined: 29 May 2009 Posts: 3 Location: hyderabad

how can modify a sql query to use one of the existing index in a table?
consider an example if a table is having 5 indexes and the query is using index no1 and i want it to use the index no3.
how can i do that ? can any one help on this ?

sushanth bobby · Posted: Tue Mar 09, 2010 7:04 pm

Hi Suresh,

Welcome to IBMMAINFRAMES!

Have you tried using the columns in the index as leading columns in your query.

To get better results, you have to post your query and index details.

Sushanth

Manshadi · New User Joined: 31 Aug 2005 Posts: 82

You should play with runstat jobs and index cardinality.

dick scherrer · Posted: Wed Mar 10, 2010 12:40 am

Hello,

If the query currently uses an index, why do you want to use a different index?

What happens if you specify ORDER BY for the column(s) that make up index no3?

GuyC · Posted: Wed Mar 10, 2010 4:35 pm

fiddling with runstats and index cardinality is tricky. If at some point you need to drop/create the table or runstats the table again, your accesspath could change back.

possibilities:
- add "or 0=1" to the criteria on the columns of the index you don't want
- add "optimize for 1 row "
- use OPTHINTs on the bind
...