I have a very large table (a few millions of rows) in my db2 database:

create table test ( c1 integer not null, c2 integer not null, c3 integer not null, c4 integer not null )

create unique index testInd on test(c2, c4)

sql1: select max(c2) from test
sql2: select min(c2) from test
when I run the sql above in db2expln, it tells me that the sql1 is using relation scan and this doesn't happen in a small table. while sq2 is Ok for small and large table.

execution time for sql1 in large table is extremely slow!!! can anyone expert help me to solve the problems. for your information i did runstats (with/without distribution) on that large table.


Please help. thanks
SzeHau

Use the ALLOW REVERSE SCAN option in the CREATE INDEX Statement

Check the SQL Reference for the correct syntax ...

HTH

Sathyaram

Any other solution other than changing the original database schema.. like tweaking the sql... because the the schema is used for cross-platform and different DBMS like informix