Systems Engineering and RDBMS

Paging of Record-Sets and COUNT(STOPKEY) Optimization

Posted by decipherinfosys on August 9, 2007

We recently received 2 questions from one of our readers regarding the paging of the record sets in Oracle and SQL Server 2005. We will cover the Oracle one in this post and will cover the SQL Server one in tomorrow’s post since it is tied to the solution that is presented in this post. The Oracle question was:

Question #1: For Oracle What is the difference between these two queries and why is option#1 faster than option#2:

Option#1:

select * from (
select x.*, rownum row_num
from (<the query> ) x
where rownum <= 100
) where row_num >= 75

Option#2:

select * from (
select x.*, rownum row_num
from (<the query> ) x
) where row_num between 75 and 100

It was argued that Option#2 gives a better performance.

Answer: Result wise, both the queries are going to return the same results. However, performance wise there is a difference. Oracle has an optimization that is called as the COUNT(STOPKEY) optimization. In option #2 above, Oracle optimizer will have to evaluate the query:

select x.*, rownum rnum
from (<the query> ) x

and then it will apply the rownum filter. In Option #1, Oracle will use the COUNT(STOPKEY) optimization to find the first 100 and will then stop and return back the results to the calling application. Let us see the execution plan to prove this:

I created a table using CTAS (Create Table AS) based on data-set from another big table. This is an un-indexed table just to prove the difference and to show how the COUNT(STOPKEY) optimization kicks in – ideally, you will never have such queries in your code.

SQL> select count(*) from big_test_tbl;

COUNT(*)
———-
3000000

Now, let us run the two SQL statements:

Option #1:
select * from (
select x.*, rownum row_num
from (select * from big_test_tbl) x
where rownum < 100
) where row_num >= 75

call count cpu elapsed
——- —— ——– ———-
Parse 1 0.00 0.00
Execute 1 0.00 0.00
Fetch 2 0.00 0.10
——- —— ——– ———-
total 4 0.00 0.10

Rows Row Source Operation
——- —————————————————
26 VIEW
100 COUNT STOPKEY
100 TABLE ACCESS FULL BIG_TEST_TBL

Look at the elapsed time and look at the number of rows processed – after the first 100, it stops and gives back the 26 records that it needs to. Time taken is pretty less as well.

Option #2:

select * from (
select x.*, rownum row_num
from (select * from big_test_tbl) x
) where row_num between 75 and 100

call count cpu elapsed
——- —— ——– ———-
Parse 1 0.00 0.00
Execute 1 0.00 0.00
Fetch 2 7.93 17.03
——- —— ——– ———-
total 4 7.93 17.03

Rows Row Source Operation
——- —————————————————
26 VIEW
3000000 COUNT
3000000 TABLE ACCESS FULL BIG_TEST_TBL

Look at the number of rows it had to process and look at the elapsed time.

This may not be an issue if your query is pretty well tuned and returns a smaller sub-set of the records say 1000 or so. However, there is a substantial performance difference as you go higher in terms of the record count for the query. So, we highly recommend that you use option #1 for your paging solution for Oracle. Tomorrow, we will take a look at the SQL Server 2005 question that was posed along the same lines and will see if anything like COUNT(STOPKEY) optimization exists in SQL Server 2005 or not. Also, there is an error with the SQL 2005 SQL in the whitepaper that we posted – there is no need to do another row_number() function outside of the inner query. We will cover that as well in detail tomorrow.

6 Responses to “Paging of Record-Sets and COUNT(STOPKEY) Optimization”

  1. […] Paging of Record-Sets and COUNT(STOPKEY) Optimization […]

  2. […] side (DB side) paging of the record-sets before – you can read more on these at these links – link1 and link2. There are many other ways of addressing paging as well – one of those methods is the […]

  3. […] will use the big_test_tbl that we have used in one of our previous blog post. Suppose that we have a nonclustered unique index on this table on the combination of these columns […]

  4. […] What my friend noticed was that the query was doing a FTS on PKT_DTL as soon as the column PKT_DTL.CANCEL_QTY was introduced in the Select list. It was not doing that if that column was omitted from the selection list. That is how this query got noticed. First thing to notice is the pagination logic in the query – we have discussed that before – the count stop key optimization. […]

  5. […] Paging of Record Sets and COUNT(STOPKEY) Optimization in Oracle: https://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-optimization/ […]

  6. […] The ROWNUM keyword is used to truncate the size of the result set after N items. Oracle will optimize the usage of ROWNUM which can result in drastical improvement of some queries. The outline of a paginated query looks […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: