Limiting the number of ordered rows retrieved in Oracle 12c


Starting with Oracle 12c You can limit the ordered result set of a query with FETCH clause after the ORDER BY clause.
Earlier You had to use analytic function for solving this problem.
Now  -with the new syntax- you can implement it with easier syntax,
but the execution plan is SAME, as you will see it.
Let’s consider the following ranking problem:
We would like to see the  first “n”   well paid people from employees
table using the traditional RANK analytic function:

SELECT  /*+ GATHER_PLAN_STATISTICS */  t.*
FROM(SELECT employee_id,last_name,salary,
RANK()
 OVER( ORDER BY  salary desc) rank from employees) t
WHERE t.rank<=2
100 King               24000         1
101 Kochhar            17000         2
102 De Haan            17000         2

As You could see that  the server fetched 3 rows because
the last 2 employee have the same salaries.
Now consider the new FETCH  opertor with FIRST/NEXT
(they are same!):

SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH NEXT 5 ROWS ONLY ;
100 King                  24000
101 Kochhar               17000
102 De Haan               17000
145 Russell               14000
146 Partners              13500

Nice feature is the OFFSET clause, which enables us to fetch next “n”rows after “m” rows:

SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees
ORDER BY salary DESC OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY  ;
201 Hartstein             13000
108 Greenberg             12008
205 Higgins               12008
147 Errazuriz             12000
168 Ozer                  11500

Now let’s see the execution plans! You will see – even the number of the rows are different – the SAME EXECUTION PLAN was used
by the optimizer, first for the RANK function:

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:275
SQL_ID  fb0u8y9g33ykp, child number 0
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS */  t.* FROM (SELECT
employee_id,last_name,salary,  RANK() OVER( ORDER BY  salary desc) rank
from employees) t  WHERE t.rank<=2

Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     3 (100)|          |
|*  1 |  VIEW                    |           |   107 |  5671 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."RANK"<=2)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC )<=2)

Operation_id:1 last ouput rows:3 Query block name: SEL$2
Operation_id:2 last ouput rows:3 Query block name: SEL$2
Operation_id:3 last ouput rows:107 Query block name: SEL$2
====================================================================================================

Let’s consider the execution plan for the first FETCH operator:
(without OFFSET option)

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:7
SQL_ID  3hp4dwmhuvp7b, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC FETCH NEXT 5 ROWS ONLY

Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     3 (100)|          |
|*  1 |  VIEW                    |           |   107 |  7062 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
)<=5)

Operation_id:1 last ouput rows:5 Query block name: SEL$1
Operation_id:2 last ouput rows:5 Query block name: SEL$1
Operation_id:3 last ouput rows:107 Query block name: SEL$1
====================================================================================================

We can observe – again – the same plan hash value used by the optimizer.

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:9
SQL_ID  1wg200mm8st4t, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS
ONLY

Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     3 (100)|          |
|*  1 |  VIEW                    |           |   107 |  7062 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  1712 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN
(5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">
5))
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
)<=CASE  WHEN (5>=0) THEN 5 ELSE 0 END +5)

Operation_id:1 last ouput rows:5 Query block name: SEL$1
Operation_id:2 last ouput rows:10 Query block name: SEL$1
Operation_id:3 last ouput rows:107 Query block name: SEL$1
=================================================================================================

The ROW_NUMBER and a searched CASE conditional expression were used.

We can see that these nice new features use the same operators
and options however they will be popular – I guess – because the
syntax is easier to learn it.

(The next entry will focus on FECTH with PERCENT feature)

About these ads

3 Responses to “Limiting the number of ordered rows retrieved in Oracle 12c”

  1. 12c First N | Oracle Scratchpad Says:

    […] nice posts about the “Top N” (or First N / Next N)  syntax that has appeared in 12c, here and here, for example. I particularly like the first set of examples because they include some […]

  2. Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting Ltée Says:

    […] Limiting the number of ordered rows retrieved […]

  3. Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting LTDDBLinks Consulting LTD Says:

    […] Limiting the number of ordered rows retrieved […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: