12c New Feature: SQL Row-Limiting Clause


selection-64197_640

Its not realy a new feature because many other database vendors have this feature already but it is new to Oracle 12c. You can now limit the Output of rows in a SQL Query.

A small example:

Check the employees Table

SQL> select count(*) from employees;

  COUNT(*)
----------
      107

Now we are only want to returning 10 Rows beginning with the lowest employee_id.

select employee_id,first_name,last_name
  from employees
order by employee_id
FETCH FIRST 10 ROWS ONLY;
EMPLOYEE_ID FIRST_NAME    LAST_NAME
----------- ------------- -------------
    100     Steven        King
    101     Neena         Kochhar
    102     Lex           De Haan
    103     Alexander     Hunold
    104     Bruce         Ernst
    105     David         Austin
    106     Valli         Pataballa
    107     Diana         Lorentz
    108     Nancy         Greenberg
    109     Daniel        Faviet

You can also use a offset for the Row Limit Clause, for example to start with the fifth record and show the next 10 rows.

select employee_id,first_name,last_name
  from employees
order by employee_id
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
EMPLOYEE_ID FIRST_NAME    LAST_NAME
----------- ------------- -------------
    105     David         Austin
    106     Valli         Pataballa
    107     Diana         Lorentz
    108     Nancy         Greenberg
    109     Daniel        Faviet
    110     John          Chen
    111     Ismael        Sciarra
    112     Jose Manuel   Urman
    113     Luis          Popp
    114     Den           Raphaely

A other way is to use PERCENT instead of ROWS. For example to show the first 5 Percent of a table.

select employee_id,first_name,last_name
  from employees
order by employee_id
FETCH FIRST 5 PERCENT ROWS ONLY;
EMPLOYEE_ID FIRST_NAME    LAST_NAME
----------- ------------- -------------
    100     Steven        King
    101     Neena         Kochhar
    102     Lex           De Haan
    103     Alexander     Hunold
    104     Bruce         Ernst
    105     David         Austin

So what happen here is that a view is created during execution and a filter on the view limits the rows to 5 percent of the total rows. You can also observe this with the execution plan

For example a simple SELECT on the emp table to show the first 10 Rows:

select empno,ename
  from emp
FETCH FIRST 10 ROWS ONLY;
Plan hash value: 3611411408

-------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |       |       |     3 (100)|          |
|*  1 |  VIEW               |      |    14 |   462 |     3    (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|      |    14 |   140 |     3    (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | EMP  |    14 |   140 |     3    (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=10)

As always comments are welcome

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s