Hello Stan, group I have now verified approaches 2, 3, and 4 from the link provided by Stan here on an Oracle 11g2 test database: http://www.inf.unideb.hu/~gabora/pagination/results.html
It seems that #3 and #4 perform equally well. #4, however, is simpler and more aligned with what is implemented for DB2, SQL Server and Sybase SQL Anywhere already. It makes use of the SQL Standard ROW_NUMBER() ranking function, which is why I prefer #4 over #3. I will thus implement #1020 in jOOQ 2.4.0: http://sourceforge.net/apps/trac/jooq/ticket/1020 Here is the test setup (any reproduction of this setup and feedback for Oracle 10g, for instance is very welcome): *Test data generation:* ------------------------------ CREATE TABLE test_table AS SELECT level as id, level as order_col, mod(level, 5) as category_col FROM dual CONNECT BY level < 1000000; CREATE INDEX test_index_1 ON test_table(order_col); CREATE INDEX test_index_2 ON test_table(category_col); ALTER TABLE test_table ADD CONSTRAINT test_pk PRIMARY KEY(id); *Technique #2: Full sort / numbering, fetch page rows only* ------------------------------ SELECT t.* /*rn-test-2*/ FROM ( SELECT ROWNUM AS rn , t.* FROM ( SELECT t.* FROM test_table t WHERE category_col = 30 ORDER BY order_col ) t ) t WHERE rn BETWEEN 991 AND 1000 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.27 | 2478 | | | | |* 1 | VIEW | | 1 | 200K| 10 |00:00:00.27 | 2478 | | | | | 2 | COUNT | | 1 | | 200K|00:00:00.22 | 2478 | | | | | 3 | VIEW | | 1 | 200K| 200K|00:00:00.18 | 2478 | | | | | 4 | SORT ORDER BY | | 1 | 200K| 200K|00:00:00.15 | 2478 | 7423K| 1083K| 6598K (0)| |* 5 | TABLE ACCESS FULL| TEST_TABLE | 1 | 200K| 200K|00:00:00.05 | 2478 | | | | ------------------------------------------------------------------------------------------------------------------------- *Technique #3: Top-N query, extended extremal values search* ------------------------------ SELECT t.* /*rn-test-3*/ FROM ( SELECT ROWNUM AS rn , t.* FROM ( SELECT t.* FROM test_table t WHERE category_col = 3 ORDER BY order_col ) t WHERE ROWNUM <= 1000 ) t WHERE rn >= 991 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.09 | 2478 | | | | |* 1 | VIEW | | 1 | 1000 | 10 |00:00:00.09 | 2478 | | | | |* 2 | COUNT STOPKEY | | 1 | | 1000 |00:00:00.09 | 2478 | | | | | 3 | VIEW | | 1 | 200K| 1000 |00:00:00.09 | 2478 | | | | |* 4 | SORT ORDER BY STOPKEY| | 1 | 200K| 1000 |00:00:00.09 | 2478 | 57344 | 57344 |51200 (0)| |* 5 | TABLE ACCESS FULL | TEST_TABLE | 1 | 200K| 200K|00:00:00.05 | 2478 | | | | ---------------------------------------------------------------------------------------------------------------------------- *Technique #4: Numbering with analytical functions, fetch relevant rows only * ------------------------------ SELECT t.* /*rn-test-4*/ FROM ( SELECT ROW_NUMBER() OVER (ORDER BY order_col) AS rn , t.* FROM test_table t WHERE category_col = 3 ORDER BY order_col ) t WHERE rn BETWEEN 991 AND 1000 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.09 | 2478 | | | | |* 1 | VIEW | | 1 | 200K| 10 |00:00:00.09 | 2478 | | | | |* 2 | WINDOW SORT PUSHED RANK| | 1 | 200K| 1001 |00:00:00.09 | 2478 | 57344 | 57344 |51200 (0)| |* 3 | TABLE ACCESS FULL | TEST_TABLE | 1 | 200K| 200K|00:00:00.05 | 2478 | | | | ----------------------------------------------------------------------------------------------------------------------------
