Systems Engineering and RDBMS

Archive for August 9th, 2007

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.

Posted in Oracle | 6 Comments »