hi,
this query eventhough it has indexes on all the columns except on timestamp columns on 2 tables its not using the indexes on devlopment server.
on the production its using the indexes,but eventhough its using the indexes its running very slow.its even taking more time on dev.
the data is same in both dev and prod. the DFT_QUERYOPT was also the same which is set to 7

1) my question is why db2 is not using the indexes on dev when its using on prod with data and indexes beeing same on both the databases.
2) what is the difference between using OR and UNION. will union improve the performance of the queries and if so how?.

any help is appreciated
thanks


SELECT a.col1,
b.col4,
c.col1,
d.col3
FROM abc.tab1 a
INNER JOIN abc.tab2 b ON a.c5 = b.c6 AND a.c2 <> 121
INNER JOIN abc.tab3 c ON ON b.c2 = c.c4 AND a.c8 = 12
INNER JOIN abc.tab4 d ON c.c7 = d.c9
WHERE ( a.c_timestamp >= '2007-06-06-08.32.45'
OR b.c_timestamp >= '2007-09-01-08.32.45'
OR c.c_timestamp >= '2007-01-01-08.32.45'
OR d.c_timestamp >= '2006-12-01-08.32.45' )

I believe there are different statistic between your dev and production.
The simplest statistic is 'how many rows in the table', 'how many rows hit by this index value'... etc.


If there is zero rows(close to zeros rows) from your development server, using index may be slower than table scan. The story will be totally different if you have millions of rows.

You can always unload the statistic from your production server to development server to mimic the environment.

Have you configured and tuned the production server already? You may want to run the DB2 Configuration Advisor to get a good base line and then do further tuning if necessary.

Apart from that, we cannot tell you anything on why things are slow if you don't provide any details like DB2 version and platform, DDL statements for tables and indexes, number of rows, whether stats are current, whether statement compilation or execution takes a long time, ... The usual stuff.