Posted by 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.
Learning is not a spectator sport 
2 comments