Archive for July, 2013

Limiting the percentage of ordered rows retrieved in Oracle 12c

July 23, 2013

In a previous post I compared two solutions for the same ranking
problem. Now I would like to compare two solutions for the same
percentages report.
The goal of the query: Let's see the first 5% rows of the total
rows according to the salary in descending way.
Let's consider the solution in Oracle 12c, where we can use the
new FETCH {FIRST|NEXT}  <pct> PERCENT ROWS clause:
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC  FETCH FIRST 5 PERCENT ROWS
ONLY;

EMPLOYEE_ID LAST_NAME      SALARY
----------- ------------ --------
100 King               24000
101 Kochhar            17000
102 De Haan            17000
145 Russell            14000
146 Partners           13500
201 Hartstein          13000

6 rows selected
One of the possible traditional solution uses
the cumulative distribution (CUME_DIST) analytic function.
SELECT /*+ GATHER_PLAN_STATISTICS */  E.*
FROM
(SELECT employee_id, last_name, salary,
CUME_DIST()  OVER( ORDER BY  Salary DESC) cum_dist
FROM employees  ORDER BY salarY DESC) E
WHERE E.CUM_DIST<=0.05;

EMPLOYEE_ID LAST_NAME     SALARY        CUM_DIST
----------- --------- ----------   -------------
100         King           24000   0.009345794393
101      Kochhar           17000   0.02803738318
102      De Haan           17000   0.02803738318
145      Russell           14000   0.03738317757
146     Partners           13500   0.04672897196
Let's consider the execution plan for that query which 
uses the new FETCH {FIRST|NEXT} <pct> PERCENT ROWS clause:
Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:1555
SQL_ID  1jnwttv1rt22u, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  employee_id, last_name, salary
FROM employees  ORDER BY salary DESC  FETCH FIRST 5 PERCENT ROWS ONLY

Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |       |       |     3 (100)|          |
|*  1 |  VIEW               |           |   107 |  8453 |     3   (0)| 00:00:01 |
|   2 |   WINDOW SORT       |           |   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"<=CEIL("from$_
subquery$_002"."rowlimit_$$_total"*5/100))

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

Now we examine the execution plan of the query that uses the CUME_DIST analytic function:

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:7
SQL_ID  0u2b7mdnfy5nh, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  E.* FROM (SELECT employee_id,
last_name, salary, CUME_DIST()  OVER( ORDER BY  Salary DESC) cum_dist
FROM employees  ORDER BY salarY DESC) E WHERE E.CUM_DIST<=0.05

Plan hash value: 720055818

---------------------------------------------------------------------------------
| 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       |           |   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("E"."CUM_DIST"<=.05)

Operation_id:1 last ouput rows:5 Query block name: SEL$2
Operation_id:2 last ouput rows:107 Query block name: SEL$2
Operation_id:3 last ouput rows:107 Query block name: SEL$2
====================================================================================================
We can observe that BOTH queries use the SAME EXECUTION plan,
but with different number of consistent gets (1551 versus 7!). 
Other metrics are same. 
I created a greater table:
CREATE TABLE big_emp(empno,last_name,first_name,salary)
AS
SELECT ROWNUM,E.last_name||ROWNUM,E.first_name||ROWNUM,E.salary
FROM employees E,employees D,employees F;
SELECT COUNTt(*)  FROM big_emp;

COUNT(*)
----------
1225043

I executed the following queries:

SELECT /*+ GATHER_PLAN_STATISTICS */  *
FROM big_emp  ORDER BY salary DESC  FETCH FIRST 1 PERCENT ROWS
ONLY;

SELECT /*+ GATHER_PLAN_STATISTICS */  E.*
FROM
(SELECT b.*,
CUME_DIST()  OVER( ORDER BY  Salary DESC) cum_dist
FROM big_emp b ORDER BY salarY DESC) E
WHERE E.CUM_DIST<=0.01;

Of course, I got the same execution plan.
But the were no big difference between the number of consistent gets.

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:6693
SQL_ID  5cv1yxs3aht9g, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  E.* FROM (SELECT b.*, CUME_DIST()
OVER( ORDER BY  Salary DESC) cum_dist FROM big_emp b ORDER BY salarY
DESC) E WHERE E.CUM_DIST<=0.01

Plan hash value: 1432758025
Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:6684 | Consistent gets:8059
SQL_ID  gg7n3x4px3tak, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */  * FROM big_emp  ORDER BY salary
DESC  FETCH FIRST 1 PERCENT ROWS ONLY

Plan hash value: 1432758025
The number of consistent gets were 
(6693- for traditional versus 8059 - new for feature)
As I saw we can use both solutions,but the FETCH clause
is more readable and user-friend.
Advertisements

Restricted access to PL/SQL subprograms in Oracle 12c

July 10, 2013

Prior to Oracle 12c everyone can refer to a subprogram (helper program) in an other PL/SQL program unit if that user has execute privilege for the helper object or owns it.
Now, in Oracle 12c the creator of the helper can determine that
which program units can refer to it, even the other users have execute privilege for the helper objects or they have the EXECUTE ANY PRIVILEGE system privilege.
Even the the owner of the helper object is same as the PL/SQL
subprogram’s owner, but if the dependent object is not entitled to use
the helper subprogram it can not refer to helper PL/SQL subprogram.
The new feature is that the helper PL/SQL subprogram can have
an ACCESSIBLE BY (subprogram1,subprogram2, …) clause where the creator can provide the access to the subprograms listed after the ACCESSIBLE BY keywords.
In the following example HR user who created the tax function provided access of the tax function to the depts procedure (owned by HR) and to depts2 owned by CZINK user.
Note that HR issued the suitable object privilege to czink.
Let’s see the definition of the tax function and the GRANT statement:

CREATE OR REPLACE FUNCTION tax(BASE NUMBER)
RETURN NUMBER
ACCESSIBLE BY (depts,czink.depts2)
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN
  S:= 0.10;
ELSIF BASE<20000 THEN
  S:=0.25;
ELSE
  S:=0.3;
END IF;
RETURN BASE*S;
END;
/
GRANT EXECUTE ON tax TO czink;

Now HR user created a procedure called depts and executed it:

CREATE OR REPLACE
PROCEDURE depts(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

However if HR wants to create a depts2 procedure with the
following code, Oracle produces an error message, because the depts2 procedure WAS NOT ENTITLED to refer to the tax function:

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
    IF r.manager_id IS NOT NULL THEN
     SELECT last_name INTO MANAGER FROM employees
     WHERE employee_id=r.manager_id;
   ELSE
    manager:='No Manager';
   END IF;
   DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
   ' tax:'|| tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
Error(17,14): PLS-00904: insufficient privilege to access object TAX

Now CZINK user wants to create and execute a depts2 procedure
referring to the tax function owned by HR:

(Supposed that CZINK user has it’s own employees table)

CREATE OR REPLACE
PROCEDURE depts2(p_deptno  employees.department_id%TYPE)
AUTHID CURRENT_USER
IS
CURSOR c_emp(c_deptno employees.department_id%TYPE) IS
SELECT e.*, 12*salary*(1+NVL(commission_pct,0)) ANN_SAL
FROM employees e WHERE department_id=c_deptno;
manager employees.last_name%TYPE;
BEGIN
FOR r  IN c_emp(p_deptno) LOOP
IF r.manager_id IS NOT NULL THEN
SELECT last_name INTO MANAGER FROM employees
WHERE employee_id=r.manager_id;
ELSE
manager:='No Manager';
END IF;
DBMS_OUTPUT.PUT_LINE(c_emp%ROWCOUNT||'. name:='||r.last_name||' salary:'||r.salary||
' tax:'|| HR.tax(r.salary) ||  ' Manager:'||manager||' '||r.manager_id||' Annual Salary:'||r.ann_sal);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('The error:'||DBMS_UTILITY.FORMAT_ERROR_STACK );
END;
/
exec depts2(90)
anonymous block completed
1. name:=King salary:24000 tax:7200 Manager:No Manager  Annual Salary:288000
2. name:=Kochhar salary:17000 tax:4250 Manager:King 100 Annual Salary:204000
3. name:=De Haan salary:17000 tax:4250 Manager:King 100 Annual Salary:204000

Of course, if CZINK user created a procedure
(referring to HR’s tax function) with different name than depts2
then CZINK user would get the same error message.
(The DBA role was assigned to the CZINK user in my example)

Comment and benefits of using the ACCESSIBLE BY clause:

1. You can provide access to a helper PL/SQL programs only for those
PL/SQL subprograms which are really need to refer to them.
2. The restriction made for PL/SQL subprograms not for users.
3. Even if a user has a DBA role or “just” the
EXECUTE ANY PROCEDURE the user won’t be able to use the helper
PL/SQL subprogram, unless it(the helper program) allows
to access directly to the invoker program.
4. You can specify the ACCESSIBLE BY clause on package level
(not for individual members), like this:

CREATE OR REPLACE PACKAGE taxes ACCESSIBLE BY (depts,czink.depts2)
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER;
END taxes;
/
CREATE OR REPLACE PACKAGE BODY taxes
IS
FUNCTION tax1(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF    BASE<4000 THEN   S:= 0.10;
ELSIF BASE<20000 THEN   S:=0.25;
ELSE  S:=0.3;
END IF;
RETURN BASE*S;
END tax1;
FUNCTION tax2(BASE NUMBER) RETURN NUMBER
IS
S NUMBER;
BEGIN
IF BASE<4000 THEN   S:= 0.10;
ELSE   S:=0.3;
END IF;
RETURN BASE*S;
END tax2;
END taxes;
/

Work with WITH option in Oracle 12c!

July 9, 2013

Starting Oracle 9.0  – according to SQL 1999 standard – Oracle introduced the WITH option which can be considered
as the extension of the inline views.
Let’s see an “old” example:

WITH
dept_costs  AS
(SELECT department_name, SUM(salary) as dept_total
FROM employees, departments
WHERE employees.department_id =
departments.department_id
GROUP BY department_name),
avg_cost  AS
(SELECT SUM(dept_total)/COUNT(*) as dept_avg
FROM   dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT dept_avg FROM  avg_cost)
ORDER BY department_name;

(The origin of this statement is Oracle course,titled:
Oracle Database: SQL Fundamentals I
Volume II • Student Guide).
In Oracle 12c we have new enhancements for this kind of SQL statement:
We can define “in-line” functions or procedures after the WITH clause.
In the following example we are looking for those departments, where
total salary of the department is greater than the maximum of the average of the total salaries:

WITH
FUNCTION tax(p_amount IN NUMBER)
RETURN NUMBER
IS
m  NUMBER;
BEGIN
IF p_amount <8000 THEN
m:=0.08;
ELSIF p_amount <18000 THEN
m:=0.25;
ELSE
m:=0.3;
END IF;
RETURN p_amount * m;
END;
emp_costs  AS (
SELECT d.department_name dept_name,e.last_name, e.salary AS salary,
tax(e.salary) AS tax_amount
FROM   employees e JOIN departments d
ON     e.department_id = d.department_id),
dept_costs    AS (
SELECT dept_name, SUM(salary) AS dept_sal,SUM(tax_amount) tax_sum,
AVG(salary) avg_sal
FROM   emp_costs GROUP BY dept_name)
SELECT * FROM   dept_costs
WHERE  dept_sal > (SELECT MAX(avg_sal) FROM dept_costs)
ORDER BY dept_name;
/

The result is:

DEPT_NAME             DEPT_SAL    TAX_SUM    AVG_SAL
------------------------------ ---------- ---------- ----------
Accounting               20308       5077      10154
Executive                58000      15700 19333.3333
Finance                  51608       9094 8601.33333
IT                       28800       3834       5760
Purchasing               24900       3862       4150
Sales                   304500      62083 8955.88235
Shipping                156400      15266 3475.55556

7 rows selected.

The execution plan is the following ( not very simple!):

Plan hash value: 38700341

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |       |       |     7 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION      |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                |                           |       |       |            |          |
|   3 |    HASH GROUP BY                |                           |    27 |   621 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                           |       |       |            |          |
|   5 |      NESTED LOOPS               |                           |   106 |  2438 |     3   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL         | EMPLOYEES                 |   107 |   749 |     3   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | DEPT_ID_PK                |     1 |       |     0   (0)|          |
|   8 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS               |     1 |    16 |     0   (0)|          |
|   9 |   SORT ORDER BY                 |                           |    27 |  1512 |     4   (0)| 00:00:01 |
|* 10 |    VIEW                         |                           |    27 |  1512 |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D6694_1CABAF |    27 |   621 |     2   (0)| 00:00:01 |
|  12 |     SORT AGGREGATE              |                           |     1 |    13 |            |          |
|  13 |      VIEW                       |                           |    27 |   351 |     2   (0)| 00:00:01 |
|  14 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6694_1CABAF |    27 |   621 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
10 - filter("DEPT_SAL">)

====================================================================================================</pre>

We can use the WITH option with plsql_declarations clause
in DML statements as well.
Let’s consider the following example where we want to compute the
tax amount of the salaries and modify the tax_amount column,
but we DON’T WANT TO USE the stored tax function if exists at all!
First we create a copy of the employees table and modify the
structure with a new column

CREATE TABLE newmp AS SELECT * FROM employees;
ALTER TABLE newemp ADD tax_amount NUMBER(10,2);

Now we modify the content of the tax_amount column
(which is originally empty) with each employee’s tax amount:

UPDATE     /*+ WITH_PLSQL */ newemp E
SET tax_amount=(WITH FUNCTION TAX(P_AMOUNT IN NUMBER)
RETURN NUMBER
IS
M  NUMBER;
BEGIN
IF P_AMOUNT <8000 THEN
M:=0.08;
ELSIF P_AMOUNT <18000 THEN
M:=0.25;
ELSE
M:=0.3;
END IF;
RETURN P_AMOUNT*M;
END;
SELECT tax(salary) FROM employees m
WHERE m.employee_id=e.employee_id)
/

Observe that the WITH statement has a special hint.
The WITH_PLSQL hint only enables you to specify
the WITH plsql_declarations clause within the statement.
It is not an optimizer hint.(see Oracle 12c documentation)

Let’s check the result (only the first 10 rows are displayed):


SELECT *
FROM
(SELECT  last_name,salary,tax_amount from newemp
ORDER BY  salary DESC)
WHERE ROWNUM<=10;

LAST_NAME                          SALARY TAX_AMOUNT
-------------------------      ---------- ----------
King                                24000       7200
Kochhar                             17000       4250
De Haan                             17000       4250
Russell                             14000       3500
Partners                            13500       3375
Hartstein                           13000       3250
Greenberg                           12008       3002
Higgins                             12008       3002
Errazuriz                           12000       3000
Ozer                                11500       2875

10 rows selected.

The execution plan for the UPDATE is the following(simpler!)

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:505
SQL_ID  6s126hzr93g2d, child number 0
-------------------------------------
UPDATE  /*+ WITH_PLSQL */ newemp E SET tax_amount=(WITH FUNCTION
TAX(P_AMOUNT IN NUMBER) RETURN NUMBER IS M  NUMBER; BEGIN IF P_AMOUNT
<8000 THEN M:=0.08; ELSIF P_AMOUNT <18000 THEN M:=0.25; ELSE M:=0.3;
END IF; RETURN P_AMOUNT*M; END; SELECT tax(salary) FROM employees m
WHERE m.employee_id=e.employee_id)

Plan hash value: 759157450

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |               |       |       |   217 (100)|          |
|   1 |  UPDATE                      | NEWEMP        |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | NEWEMP        |   107 |  1819 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |     8 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------------

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

4 - access("M"."EMPLOYEE_ID"=:B1)

====================================================================================================

The greatest benefit of using PL/SQL functions or procedures that we
can use our (not stored) subprograms in queries without writing
procedural code.

Limiting the number of ordered rows retrieved in Oracle 12c

July 9, 2013

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)