When executing the following sql derby does not use UPDATED COLUMN's index
and the execution is very very slow.
SELECT A.ID, A.CONTRACTDATE, A.UPDATED, B.NAME FROM CONTRACT A LEFT JOIN
CUSTOMER B ON A.CUST_ID = B.ID ORDER BY A.UPDATED DESC OFFSET 0 ROWS FETCH
NEXT 10 ROWS ONLY
CONTRACT table contains about 10,0000 rows and CUSTOMER TABLE contains
about 2,000 rows. UPDATED COLUMN is a timestamp type and has a desc index.
I check the execution plan and it shows that sorting is external and does
not use the UPDATED COLUMN index.
But execution of the next sql is very fast:
SELECT A.ID, A.CONTRACTDATE, A.UPDATED, B.NAME FROM CONTRACT A ORDER BY
A.UPDATED DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Why?
Thanks,
Hui Li
