Hi,

We have one query running for hours and not completing. After analyzing we found one index(eg: Index1) was culprit, after dropping that index query ran within 15 mins(using another index eg: Index2).

But we cannot permanently remove Index1, as it is required for few other queries.

So is there any way for us to make the query always use Index2.

We tried reorg by choosing Index2, but also no effect.

Any suggestions are welcome!!!!

Thanks,
Dharmaraj.G

Which DB2 version and OS?

Andy

I am not sure but you can try

- to use design/index advisor on this query and see what it says;
- if a query is in a package you can try to change optimization level when you bind your package and it might change the indexes it is using;

my 5 cents

@ Andy

DB2 version is 8.1 fixpack 12
OS version is AIX 5.3

@Markham

The query is not from package, it is a dynamic query and index advisor didnt help us...

You can try to use the SELECTIVITY clause:

DB2 Universal Database

Andy

Thanks for your reply. I am going through SELECTIVITY clause.

Will execute and get back!!

You can also play with the statistics for both indexes. (Which raises the question whether your statistics are up to date in the first place.)