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.