db INDEX on ORDER BY FIELD in very simple query does not help - still slow response? I have very simple query which returns 200K records very slow (20 seconds).

SELECT * FROM TABLE ORDER BY ID DESC


If I do just

 SELECT * FROM TABLE


it returns quick result.

I created INDEX on that field ID (ALLOW REVERSE SCANS) but still returns very similar response. Where can be the problem? What can be the cause of stagnation for this query? I updated statictics and index table metadata.

Shouldn't INDEX help me on this ORDER BY FIELD?

My server has 8GB RAM.
Buffer pool has value of 128MB. I adjusted it to 1 GB but still no too much progress.

I am using DB2 database.

Configuration Parameters are with this values: (SORTHEAP) = AUTOMATIC(164) and (SHEAPTHRES_SHR) = AUTOMATIC(824).  (SHEAPTHRES) = 0. Should I maybe adjust them and on what values?

This is live system in production so I will be very thankful for help.

Thank you
Well, first of all a SELECT * will pretty much void using any indexes. DB2 will see that you require the entire table and thus do a table scan (which in itself can be expensive).

After that you are doing a an ORDER BY, which causes DB2 to do a SORT on the table it just scanned (another expensive operation). So you have two expensive operations one after the other.

Another thing.....if ID is the primary key...it already built an index for you under the covers. (which again it will ignore with a SELECT *). So you now have a second index that is probably just taking up disk space. (Just as an fyi any field that has a unique constraint-including the primary key-DB2 automatically builds a unique index for you.)

So first off, what this really speaks to is a bad query (no other way to put it). Do you have any predicates to add to the SQL? If you can cut down what you are selecting with a WHERE clause, or if you don't need every column in the SELECT, then you could build an index or indexes over those columns and that would help.

I would advise you to do an EXPLAIN over this query so you can see the cost and the query plan that DB2 used. Then as you test out indexes, etc, you can see if DB2 chooses to take advantage of them or not, and which is the most efficient method.