1z0-060 Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Solaris: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL> explain plan for
  2  select * from hr.employees
  3  fetch first 20 percent rows only;

Explained.

SQL> set pages 999
SQL> set lin 120
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 48081388

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   107 | 17013 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |           |   107 | 17013 |     3   (0)| 00:00:01 |
|   2 |   WINDOW BUFFER     |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_
              subquery$_002"."rowlimit_$$_total"*20/100))

16 rows selected.

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever';

Session altered.

SQL> select * from hr.employees
  2  fetch first 20 percent rows only;

22 rows selected.

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

SQL> select 107*0.2 from dual;

   107*0.2
----------
      21.4
     
SQL> select value from v$diag_info where name='Default Trace File';

VALUE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4477.trc

SQL> exit

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."EMPLOYEE_ID" "EMPLOYEE_ID",
    "from$_subquery$_002"."FIRST_NAME" "FIRST_NAME",
    "from$_subquery$_002"."LAST_NAME" "LAST_NAME",
    "from$_subquery$_002"."EMAIL" "EMAIL",
    "from$_subquery$_002"."PHONE_NUMBER" "PHONE_NUMBER",
    "from$_subquery$_002"."HIRE_DATE" "HIRE_DATE",
    "from$_subquery$_002"."JOB_ID" "JOB_ID",
    "from$_subquery$_002"."SALARY" "SALARY",
    "from$_subquery$_002"."COMMISSION_PCT" "COMMISSION_PCT",
    "from$_subquery$_002"."MANAGER_ID" "MANAGER_ID",
    "from$_subquery$_002"."DEPARTMENT_ID" "DEPARTMENT_ID"
    FROM 
        (SELECT "EMPLOYEES"."EMPLOYEE_ID" "EMPLOYEE_ID",
            "EMPLOYEES"."FIRST_NAME" "FIRST_NAME",
            "EMPLOYEES"."LAST_NAME" "LAST_NAME",
            "EMPLOYEES"."EMAIL" "EMAIL",
            "EMPLOYEES"."PHONE_NUMBER" "PHONE_NUMBER",
            "EMPLOYEES"."HIRE_DATE" "HIRE_DATE",
            "EMPLOYEES"."JOB_ID" "JOB_ID",
            "EMPLOYEES"."SALARY" "SALARY",
            "EMPLOYEES"."COMMISSION_PCT" "COMMISSION_PCT",
            "EMPLOYEES"."MANAGER_ID" "MANAGER_ID",
            "EMPLOYEES"."DEPARTMENT_ID" "DEPARTMENT_ID",
            ROW_NUMBER() OVER ( ORDER BY  NULL ) "rowlimit_$$_rownumber",
            COUNT(*) OVER () "rowlimit_$$_total"
            FROM "HR"."EMPLOYEES" "EMPLOYEES"
            ) "from$_subquery$_002"
    WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*20/100
)