\n
![\"2015-10-02](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2015-10-02-16.45.51.jpg\")
At our bi-annual dbiXchange I was talking with Nicolas Jardot<\/a> about his presentation on Result Cache (don’t forget J\u00e9rome witt<\/a> session about RC at DOAG<\/a>) where he has shown an unexpected behavior on ‘fetch first n rows queries’.\nThat behavior – if it is not a bug – can also be a good thing when using offset queries.
\n<\/p>\n
The case<\/h3>\n
Everything is in the execution plan:<\/p>\n
\nPLAN_TABLE_OUTPUT\n---------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 799vsxdg75sm6, child number 0\n-------------------------------------\nselect \/*+ result_cache *\/ * from DEMO order by n fetch first 5 rows only\n \nPlan hash value: 896528075\n \n--------------------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |\n--------------------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:02.49 | 376 | 207 |\n|* 1 | VIEW | | 1 | 100K| 5 |00:00:02.49 | 376 | 207 |\n| 2 | RESULT CACHE | aanuwt05phj34078f253ht7x0x | 1 | | 100K|00:00:02.25 | 376 | 207 |\n| 3 | WINDOW NOSORT | | 1 | 100K| 100K|00:00:01.72 | 376 | 207 |\n| 4 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 100K| 100K|00:00:00.99 | 376 | 207 |\n| 5 | INDEX FULL SCAN | DEMO_PK | 1 | 100K| 100K|00:00:00.25 | 210 | 207 |\n--------------------------------------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n 1 - filter(\"from$_subquery$_002\".\"rowlimit_$$_rownumber\"<=5)\n \nResult Cache Information (identified by operation id):\n------------------------------------------------------\n 2 - column-count=4; dependencies=(DEMO.DEMO); attributes=(ordered); name="select \/*+ result_cache *\/ * from DEMO "\n<\/code><\/pre>\nI want to fetch only the first 5 rows, I access through an index scan so that I don’t need a sort. Then I expect to read only the 5 first entrie in the index – only a few blocks.
\nI want to use the result cache in case I run my query again. That should increase the performance of subsequent runs, but should not decrease the performance of the first run – except the small overhead to put 5 rows into the result cache.<\/p>\n
But look at it again: I’ve read 100000 rows. The whole table. And 100000 have gone to the result cache:<\/p>\n
\nSQL> select id,type,status,cache_id,row_count,name from v$result_cache_objects;\n \n ID TYPE STATUS CACHE_ID ROW_COUNT NAME\n---------- ---------- --------- ------------------------------ ---------- ----------------------\n 0 Dependency Published DEMO.DEMO 0 DEMO.DEMO\n 1 Result Published a46rp35xsfhzg6ukq622ax96xh 100000 select \/*+ result_cach\n<\/code><\/pre>\nGood or bad?<\/h3>\n
My first idea is that it is a bug. When I put a ‘result_cache’ hint, I expect the final result to go to result cache. Not an intermediate one. If I want an intermediate one, I can put the hint in a subquery. Of course, it’s bad to read all table rows when I explicitly want only 5 ones.<\/p>\n
I addition to that, I expected that the behavior here would be the same as when forcing the table result cache mode. But it’s not the case. Setting ‘result_cache (mode force)’ instead of using the result_cache hint caches the final result – the 5 rows.
\nLook, when setting both, I’ve two results going to the cache:<\/p>\n
\nSQL> alter table DEMO result_cache (mode force);\nTable altered.\n \nSQL> select \/*+ result_cache *\/ * from DEMO order by n fetch first 5 rows only;\n \n N X\n---------- ----------\n 1 1\n 2 .5\n 3 .3\n 4 .3\n 5 .2\n \nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 799vsxdg75sm6, child number 0\n-------------------------------------\nselect \/*+ result_cache *\/ * from DEMO order by n fetch first 5 rows only\n \nPlan hash value: 896528075\n \n------------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\n------------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:02.46 | 376 |\n| 1 | RESULT CACHE | 2j3s6quuam85248yh8458tcprb | 1 | | 5 |00:00:02.46 | 376 |\n|* 2 | VIEW | | 1 | 100K| 5 |00:00:02.46 | 376 |\n| 3 | RESULT CACHE | ch5d2dt62d5n485utqj03pftw2 | 1 | | 100K|00:00:02.22 | 376 |\n| 4 | WINDOW NOSORT | | 1 | 100K| 100K|00:00:01.70 | 376 |\n| 5 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 100K| 100K|00:00:00.97 | 376 |\n| 6 | INDEX FULL SCAN | DEMO_PK | 1 | 100K| 100K|00:00:00.24 | 210 |\n------------------------------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n 2 - filter(\"from$_subquery$_002\".\"rowlimit_$$_rownumber\" select id,type,status,cache_id,row_count,name from v$result_cache_objects;\n \n ID TYPE STATUS CACHE_ID ROW_COUNT NAME\n---------- ---------- --------- ------------------------------ ---------- ----------------------\n 0 Dependency Published DEMO.DEMO 0 DEMO.DEMO\n 1769 Result Published 9jdwtku77k9ap60x1anfqsd2ny 100000 select \/*+ result_cach\n 1768 Result Published b4aa0hncfmd7bcdxnwf4mdyyj0 5 select \/*+ result_cach\n<\/code><\/pre>\nIt think it’s bad. I’ll open a SR about it.<\/p>\n
Pagination queries and Offset<\/h3>\n
When you have hundreds of lines to display to the user, you use pagination: display the first 15 lines with a ‘next’ button. Then the user can display the 15 next lines with the ‘next’ button, etc.
\n![\"Terminal-ibm3486\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Terminal-ibm3486.jpg\")
\nIn the days where a user had only one screen, that was easy. You run the query, get a cursor, fetch 15 lines from it. When the user press ‘next’ function we fetch 15 more lines. Exactly as when you use ‘set pause on’ in sqlplus.
\nThat was perfect. But that changed. Still in client\/server but on Windows, the users were able to run several applications at a time. They can open a cursor, and let it open for hours or even days. Then a new requirement came: a transaction must match the user interaction, or we leave too many resources idle.
\nIt was even worse with web applications where you can start a use case and never finish it. Or use the ‘back’ button the the browser and break the flow of the application. The solution is stateless sessions. But then you can leave the cursor open. Or timeouts, but the user don’t like to re-start from begining because he has an urgent phone call.
\n![\"Do](\"http:\/\/dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2015\/10\/no-offset-banner.white_.png\")
<\/a>
\nWith stateless sessions, you have to re-run the query. Whether you use rownum, row_number() or ‘fetch first … rows’ (see when you need the first_rows() hint in a previous blog post<\/a>) the problem is that the query for the second fetch will need to read 30 lines and skip the first 15 ones. This is not optimal. More info from Markups Winand at http:\/\/use-the-index-luke.com\/no-offset<\/a>.
\nSo the basic advice is: don’t use offset.<\/p>\nResult Cache<\/h3>\n
However, we can take an advantage of the fact that result cache stores all the rows. The first run will put all rows in result cache and display only the first page. The second run will get the next rows from the result set without the need to re-run the query.
\nHere is the first page:<\/p>\n
\nSQL> variable next number\nSQL> variable offset number\nSQL> exec :offset := 0 ; :next:=5\n \nPL\/SQL procedure successfully completed.\n \nSQL> select \/*+ result_cache *\/ * from DEMO order by n offset :offset rows fetch next :next rows only;\n \n N X\n---------- ----------\n 1 1\n 2 .5\n 3 .3\n 4 .3\n 5 .2\n \nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n \nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 36gjax1bq229s, child number 0\n-------------------------------------\nselect \/*+ result_cache *\/ * from DEMO order by n offset :offset rows\nfetch next :next rows only\n \nPlan hash value: 1397896352\n \n---------------------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |\n---------------------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:02.51 | 376 | 208 |\n|* 1 | FILTER | | 1 | | 5 |00:00:02.51 | 376 | 208 |\n|* 2 | VIEW | | 1 | 100K| 5 |00:00:02.51 | 376 | 208 |\n| 3 | RESULT CACHE | 18fnpv7tfn444bghaxs5mb20kk | 1 | | 100K|00:00:02.26 | 376 | 208 |\n| 4 | WINDOW NOSORT | | 1 | 100K| 100K|00:00:01.74 | 376 | 208 |\n| 5 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 100K| 100K|00:00:01.00 | 376 | 208 |\n| 6 | INDEX FULL SCAN | DEMO_PK | 1 | 100K| 100K|00:00:00.25 | 210 | 208 |\n---------------------------------------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 1 - filter(:OFFSET=0) THEN FLOOR(TO_NUMBER(TO_CHAR(:OFFSET))) ELSE 0 END +:NEXT)\n 2 - filter((\"from$_subquery$_002\".\"rowlimit_$$_rownumber\"=0) THEN\n FLOOR(TO_NUMBER(TO_CHAR(:OFFSET))) ELSE 0 END +:NEXT AND \"from$_subquery$_002\".\"rowlimit_$$_rownumber\">:OFFSET))\n<\/code><\/pre>\nHere is the second page:<\/p>\n
\nSQL> exec :offset := 5 ; :next:=5\n \nPL\/SQL procedure successfully completed.\n \nSQL> select \/*+ result_cache *\/ * from DEMO order by n offset :offset rows fetch next :next rows only;\n \n N X\n---------- ----------\n 6 .2\n 7 .1\n 8 .1\n 9 .1\n 10 .1\n \nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 36gjax1bq229s, child number 0\n-------------------------------------\nselect \/*+ result_cache *\/ * from DEMO order by n offset :offset rows\nfetch next :next rows only\n \nPlan hash value: 1397896352\n \n--------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |\n--------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.48 |\n|* 1 | FILTER | | 1 | | 5 |00:00:00.48 |\n|* 2 | VIEW | | 1 | 100K| 5 |00:00:00.48 |\n| 3 | RESULT CACHE | 18fnpv7tfn444bghaxs5mb20kk | 1 | | 100K|00:00:00.24 |\n| 4 | WINDOW NOSORT | | 0 | 100K| 0 |00:00:00.01 |\n| 5 | TABLE ACCESS BY INDEX ROWID| DEMO | 0 | 100K| 0 |00:00:00.01 |\n| 6 | INDEX FULL SCAN | DEMO_PK | 0 | 100K| 0 |00:00:00.01 |\n--------------------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 1 - filter(:OFFSET=0) THEN FLOOR(TO_NUMBER(TO_CHAR(:OFFSET))) ELSE 0 END +:NEXT)\n 2 - filter((\"from$_subquery$_002\".\"rowlimit_$$_rownumber\"=0) THEN\n FLOOR(TO_NUMBER(TO_CHAR(:OFFSET))) ELSE 0 END +:NEXT AND\n \"from$_subquery$_002\".\"rowlimit_$$_rownumber\">:OFFSET))\n<\/code><\/pre>\nThe second run had no rows to read from the table.<\/p>\n
If we know that the user will never go further than a few pages, the we can add a subquery with rownum.<\/p>\n
Here is a pagination query that get at maximum 30 rows paged 5 by 5:<\/p>\n
\nSQL> variable next number\nSQL> variable offset number\nSQL> exec :offset := 0 ; :next:=5\nPL\/SQL procedure successfully completed.\n \nSQL> select * from (\n 2 select \/*+ result_cache *\/ * from (\n 3 select * from DEMO order by n fetch first 30 rows only\n 4 ) order by n offset :offset rows fetch next :next rows only\n 5 )\n 6 \/\n N X\n---------- ----------\n 1 1\n 2 .5\n 3 .3\n 4 .3\n 5 .2\n \nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n \nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 1yubnzwpd2z2g, child number 0\n-------------------------------------\nselect * from ( select \/*+ result_cache *\/ * from ( select * from\nDEMO order by n fetch first 30 rows only ) order by n offset :offset\nrows fetch next :next rows only )\n \n------------------------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |\n------------------------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 5 | 1 |\n|* 1 | FILTER | | 1 | | 5 |00:00:00.01 | 5 | 1 |\n|* 2 | VIEW | | 1 | 30 | 5 |00:00:00.01 | 5 | 1 |\n| 3 | RESULT CACHE | gbwdtyz67n3kk0qkgw86s4jk67 | 1 | | 30 |00:00:00.01 | 5 | 1 |\n| 4 | WINDOW NOSORT | | 1 | 30 | 30 |00:00:00.01 | 5 | 1 |\n| 5 | VIEW | | 1 | 30 | 30 |00:00:00.01 | 5 | 1 |\n|* 6 | VIEW | | 1 | 30 | 30 |00:00:00.01 | 5 | 1 |\n|* 7 | WINDOW NOSORT STOPKEY | | 1 | 100K| 30 |00:00:00.01 | 5 | 1 |\n| 8 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 100K| 31 |00:00:00.01 | 5 | 1 |\n| 9 | INDEX FULL SCAN | DEMO_PK | 1 | 100K| 31 |00:00:00.01 | 3 | 1 |\n------------------------------------------------------------------------------------------------------------------------------------\n \nSQL> exec :offset := 5 ; :next:=5\nPL\/SQL procedure successfully completed.\n \nSQL> select * from (\n 2 select \/*+ result_cache *\/ * from (\n 3 select * from DEMO order by n fetch first 30 rows only\n 4 ) order by n offset :offset rows fetch next :next rows only\n 5 )\n 6 \/\n N X\n---------- ----------\n 6 .2\n 7 .1\n 8 .1\n 9 .1\n 10 .1\n \nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n \nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 1yubnzwpd2z2g, child number 0\n-------------------------------------\nselect * from ( select \/*+ result_cache *\/ * from ( select * from\nDEMO order by n fetch first 30 rows only ) order by n offset :offset\nrows fetch next :next rows only )\n \n-----------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |\n-----------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 |\n|* 1 | FILTER | | 1 | | 5 |00:00:00.01 |\n|* 2 | VIEW | | 1 | 30 | 5 |00:00:00.01 |\n| 3 | RESULT CACHE | gbwdtyz67n3kk0qkgw86s4jk67 | 1 | | 30 |00:00:00.01 |\n| 4 | WINDOW NOSORT | | 0 | 30 | 0 |00:00:00.01 |\n| 5 | VIEW | | 0 | 30 | 0 |00:00:00.01 |\n|* 6 | VIEW | | 0 | 30 | 0 |00:00:00.01 |\n|* 7 | WINDOW NOSORT STOPKEY | | 0 | 100K| 0 |00:00:00.01 |\n| 8 | TABLE ACCESS BY INDEX ROWID| DEMO | 0 | 100K| 0 |00:00:00.01 |\n| 9 | INDEX FULL SCAN | DEMO_PK | 0 | 100K| 0 |00:00:00.01 |\n-----------------------------------------------------------------------------------------------------------------\n<\/code><\/pre>\nThe first run reads 30 rows, put them into the result cache and returns the first 5. The second run get the first 10 rows from result cache, skips the first 5 and returns next 5 ones.<\/p>\n
So what?<\/h3>\n