Result of following query is weird to me, (I' m new to DB2). It sorted the result by column in the table which I didn't mention in the query.
WITH RESULT AS (
SELECT T1.*, ROWNUMBER() OVER() AS RNUM
FROM TableNAmeT1
WHERE column1= 'xyz'
)
SELECT *
FROM RESULT
WHERE RNUM BETWEEN 1 AND 100
ORDER BY ORDER OF RESULT OPTIMIZE FOR 100 ROWS
It doesn't sort when I use following query
WITH RESULT AS (
SELECT T1.*, ROWNUMBER() OVER() AS RNUM
FROM TableNAmeT1
WHERE column1= 'xyz'
)
SELECT *
FROM RESULT
ORDER BY ORDER OF RESULT
- Is this because of the index of the table or anything else, normally these tables are FIFO
** Clearly this is due to the both “OPTIMIZE FOR 100 ROWS “ clause and use of scalar value(ROWNUMBER() for filtering (in WHERE clause).
** If I narrow down my question further, I want to know what is reason for difference order of first query and second query.
Thanks all
when you do not explicitly specify a 'order by' the result is unordered, that is, which is: data is returned as it is fetched. Normally this resultes in the order of the used index.
You may want to "explain" the statements to see, what db2 is actually performing and what indexes it is using. (db2exfmt -d database -e user -t -v % -w -1 -s % -# 0 -n % -g OTIC)