12c FETCH PERCENT

A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset.

Here’s a simple example showing the syntax


SQL> select *
  2  from t
  3  order by 1
  4  fetch first 8 rows only;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8

8 rows selected.

You can also use the keyword “PERCENT” to retrieve a percentage of the rows, as show below


SQL> select *
  2  from t
  3  order by 1
  4  fetch first 10 percent rows only;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

In our table we have 100 rows, so naturally we expect (and get) 10 rows back from the query.

But let us explore a little further. What if the table has 101 rows ? That raises the question – what is 10 percent of 101 rows ? Mathematically, we want to get “10.1” rows back from the table.


SQL> insert into t values (101); 

1 row created. 

SQL> select *
  2  from t
  3  order by 1
  4  fetch first 10 percent rows only;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

11 rows selected.

So why did we get 11 rows? If we look at the execution plan, we get a clue as to the logic


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   100 |  5200 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |      |   100 |  5200 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |      |   100 |   300 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

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

Notice the use of CEIL in the filter. In effect, we’re always rounding up. It is worth keeping this in mind, because if you have been using your own methods in the past, you may have applied a slightly different interpretation, and hence got slightly different results. For example, techniques such as the ones below


SQL> select *
  2  from
  3  (
  4  select r, 100*r/101 pct
  5  from t
  6  order by r
  7  )
  8  where pct <= 10;

         R        PCT
---------- ----------
         1  .99009901
         2 1.98019802
         3 2.97029703
         4 3.96039604
         5 4.95049505
         6 5.94059406
         7 6.93069307
         8 7.92079208
         9 8.91089109
        10  9.9009901

10 rows selected.

SQL> select *
  2  from t t_outer
  3  where (
  4    select count(*)
  5    from t t_inner
  6    where t_inner.r <= t_outer.r )/101 <= 0.10
  7  order by 1;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

are just as valid as using FETCH PERCENT but they interpret the boundary line slightly differently.

One thought on “12c FETCH PERCENT

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