Hello!

My knowledge of SQL is limited, so thanks for reading and hopefully responding to this simple question on DB2 for Windows.

I have a large warehouse table (3 mln rows with timestamps) and I need to select distinct values from a column (character type). A query SELECT (COLNAME) FROM TABNAME takes over (40 seconds). In these 3 mln rows, there are only 50 distinct values, and I'm only concerned with distinct values in the most recent (say 1000) rows. Unfortunately, a sorted query with an ORDER BY clause using FETCH FIRST N ROWS executes equally slow, probably because timestamp is written in a character column (another story).

What options do I have?

Thanks!

Sergei

1. create index on column colname something like:
create index on schema.myindex on schema.tablename (colname desc allow reverse scans)

2. run statistics:
runstats on table schema.tablename

3. run sql:
SELECT DISTINCT COLNAME FROM TABNAME

What is the result?

Hope this helps,
Grofaty

Concerning your second solution:
Even when an index is present on "timestamps", using FETCH FIRST 1000 ROWS combined with ORDER BY might not use the index if the optimizer judges that sorting a 3-mln row table is faster than doing an indexed scan. (The "FETCH FIRST N ROWS" does not influence the optimizer's choice.)
In that case it might help to add "OPTIMIZE FOR 1 ROW" to the query.
Alternatively, use an index on "timestamps" and a WHERE condition something like "timestamps > CAST(current date - 7 days AS char(10))".