Resultset Paging & Performance increase using ROWNUM

Hello.

But if we modify Chris's original posting :

[B]

Quote:

---

Originally posted by chrisrlong

SELECT
--C1,
--C2
FROM
--(
--SELECT
----C1,
----C2,
----ROWNUM
------AS QUERY_ROWNUM
--FROM
----TABLE
--WHERE
----ROWNUM < Y
--/* You can add an ORDER BY here if you want */
--) T1
WHERE
--T1.QUERY_ROWNUM > X

---

and include Jurii's sugestions:

SELECT C1,C2

FROM
(
SELECT
--inner.C1,
--inner.C2,
--inner.QUERY_ROWNUM
FROM (SELECT C1, C2, ROWNUM AS QUERY_ROWNUM
FROM table1
ORDER BY condition
) inner
WHERE inner.QUERY_ROWNUM < N+k
) outer

WHERE outer > N


Will this be a solution?

Sorry for a little bug:
Last row:

WHERE outer.ROWNUM > N

Seralex, sorry to dissapoint you but even this sollution will not work. To explain why this will have to be somewhat longer message, I'll try to demonstrate it in more detail with some output (slightlly modified for better readability in this web forum) from a real SQL*Plus session with some explain plans. Here it goes.

** I have a table TABLE1 with 10 rows:
SQL> SELECT c1 FROM table1;

C1
----------
3
1
5
8
6
7
2
9
4
10
10 rows selected.

An ordered resultset:
--SQL> SELECT c1 FROM table1 ORDER BY c1;

C1
----------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

** I want to get the rows rows in positions 4 to 7 in my sliding window. My sollution:

SQL> SELECT c1 FROM
2 (SELECT * FROM table1 ORDER BY c1)
3 WHERE ROWNUM < 8
4 MINUS
5 SELECT c1 FROM
6 (SELECT * FROM table1 ORDER BY c1)
7 WHERE ROWNUM < 4;

C1
----------
4
5
6
7

** The result is what we expected. However performace-vise this is not an optimal sollution, it has to perform on table scan up to teh upper limmit and one up to the lower limit of the sliding window. Now let us see how Chris's solution works:

SQL> SELECT c1 FROM
2 (SELECT c1, ROWNUM AS query_rownum
3 FROM TABLE1
4 WHERE ROWNUM < 8
5 order by c1
6 ) t1
7 WHERE t1.query_rownum >= 4;

C1
----------
2
6
7
8

** Obviously this is not what we expected. Let's see why by looking at the explain plan:

Execution Plan
-------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 SORT (ORDER BY)
3 2 COUNT (STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'TABLE1'

** See, The sorting is done *after* the ROWNUMs are assigned (COUNT (STOPKEY))!

** Let's see what your last proposed sollution does:
SQL> SELECT outer.c1 FROM
2 (SELECT c1, query_rownum query_rownum FROM
3 (SELECT c1, ROWNUM AS query_rownum FROM table1
4 ORDER BY c1
5 ) inner
6 WHERE inner.QUERY_ROWNUM < 8
7 ) outer
8 WHERE outer.query_rownum >= 4;

C1
----------
2
6
7
8

** Again wrong result. Let's see the reason by looking at explain plan again:

Execution Plan
-------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 SORT (ORDER BY)
3 2 COUNT
4 3 TABLE ACCESS (FULL) OF 'TABLE1'

We can see optimizer has merged bot inner and outer view in a single view and again, sorting is done *after* the original rownums are assigned.

Shure, wee could cheet and persuade the optimizer with some hints or by implementing sorting with GROUP BY to get the expected result , however I don't think it is worth the effort.

HTH,

Wow. Nice comeback. I fully admit that my first example was incorrect. It is a shortcut I use when I don't use the ORDER BY (or know I'm using an index that returns the rows in order before the ORDER BY) and then erroneously stuck that comment in there about where one could add an ORDER BY.

My bad. I apologize.

Having said that, I still hold firm that my method is faster.

To prove this, I created the same table with the same rows in the same order. I then correctly amended my statement as follows:

SELECT
---OUTER.C1
FROM
---(
------SELECT
---------INNER.C1,
---------ROWNUM
------------AS QUERY_ROWNUM
------FROM
---------(
---------SELECT
------------C1
---------FROM
------------TABLE1
---------ORDER BY
------------C1
---------) INNER
------WHERE
---------ROWNUM (LESS THAN) 8
---) OUTER
WHERE
---OUTER.QUERY_ROWNUM >= 4

Where I moved *all* ROWNUM manipulation outside of the SELECT with the ORDER BY. This yields the proper results and the following plan:

SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=3 Card=164 Bytes=4264)
VIEW (Cost=3 Card=164 Bytes=4264)
COUNT (STOPKEY)
VIEW (Cost=3 Card=164 Bytes=2132)
SORT (ORDER BY STOPKEY) (Cost=3 Card=164 Bytes=2132)
TABLE ACCESS (FULL) OF TABLE1 (Cost=1 Card=164 Bytes=2132)

(Minor Note: my optimizer default is CHOOSE and since I wanted a cost, I added the ALL_ROWS hint *for that reason only*. This hint did not change the plan at all)

For comparison, I provide the plan for the MINUS implementation:

SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=8 Card=328 Bytes=4264)
MINUS
SORT (UNIQUE NOSORT) (Cost=4 Card=164 Bytes=2132)
COUNT (STOPKEY)
VIEW (Cost=3 Card=164 Bytes=2132)
SORT (ORDER BY STOPKEY) (Cost=3 Card=164 Bytes=2132)
TABLE ACCESS (FULL) OF TABLE1 (Cost=1 Card=164 Bytes=2132)
SORT (UNIQUE NOSORT) (Cost=4 Card=164 Bytes=2132)
COUNT (STOPKEY)
VIEW (Cost=3 Card=164 Bytes=2132)
SORT (ORDER BY STOPKEY) (Cost=3 Card=164 Bytes=2132)
TABLE ACCESS (FULL) OF TABLE1 (Cost=1 Card=164 Bytes=2132)

While this may not seem like much of a difference on this 10-row table, it will become a massive difference with 10,000 row result sets. Basically, it has to run the entire statement twice and then do a MINUS on top of it all.

So while I applaud the ingenuity of the MINUS implementation, I would not recommend it as the optimal solution.

Good debate, however, and good job catching my mistake. (/me tips hat) :)


- Chris

Out of way question: If any one also working with SQl server.
Is their any way to do where rownum<10 say ??
I mean is their any functionality for rownum in SQlserver 7.0 or in sql server2000 ??
I need help on this... please.

Thanks in advance
Sonali

Yes. While I haven't worked with SQLServer for a few years, there definitely was the same concept, although I don't think it was called Rownum. Unfortunately, that's all I remember. :)

- Chris

Chris (et all),

Your point entirelly taken! I admit, loud and clear: I was jumping to conclusions too soon, this last one is a totaly viable solution with best performance possible.

In fact, when analysing your solution (with the seralex's proposal of adding another view inside a view), I made a mistake. I referenced "WHERE inner.QUERY_ROWNUM < 8" instead of simply "WHERE ROWNUM < 8", which makes a huge difference and yields the correct result.

I can say I was enjoying this discussion too!

PS. Am I wrong or does this forum software now allow "less than" and "more than" characters? It still trims leading spaces off, though... :-((

Hi

I applaud to you friends, nice discussion, Chris and Jurii were a hit.

As to Sonali's question, in SQL Server you have to write the following query to get first 10 results:

SELECT TOP 10 c1 FROM table1 ORDER BY c1

I think SQL Server firsts sorts the results, and then applies TOP 10.

Thank you friends for a nice debate!
Seralex

I found out something today...
I don't know where one can use.

I found that the ROWNUM is assigned by the where condition, and then we can use the same in
GROUP-HAVING condition

select max(work_id), rownum
from mwebwork
where WORK_LEVEL in(1,2,3,4,5)
group by rownum having rownum=2 or rownum=4 or rownum=8 or rownum=16 or rownum=32;

This way one can say which rownum to select.