This post is part 5 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive the series, please see part 1.
All the Series Parts
Published
Part 1 – write less configuration with SQL*Loader Express Mode
Part 2 – write less application code with Identity Columns
Part 3 – write less application code with In-Database Archiving
Part 4 – write less application code with Temporal Validity
Part 5 – write less code in SQL with Row Limiting
Part 6 – write less “inappropriately located” code with SELECT FROM Package-Level Collection Types
Part 7 – write less code in SQL with Lateral Inline Views
Part 8 – write less “inappropriately located” code with PL/SQL in the WITH Clause
Task #5
Pagination is the ability to retrieve an ordered result set one “page” at a time; for example, getting the first 20 records in one query execution, getting the second page (records 21-40) in another execution, or even jumping to page 9 and getting records 161-180 in a third query execution.
A Pre-12c Solution
Before 12c we can implement pagination using inline views and the ROWNUM pseudo column (or analytic functions like ROW_NUMBER), and the result is quite cumbersome. Let’s see such a solution:
We start with the complete result set, with no pagination:
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id;
PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT
---------- ---------- ------------- ---------- ----------
1 101 39 28/09/2015 30/09/2015
1 101 38 22/09/2015 24/09/2015
1 101 37 16/09/2015 18/09/2015
1 101 40 04/10/2015 06/10/2015
1 101 41 10/10/2015 12/10/2015
1 102 44 06/10/2015 08/10/2015
1 102 43 28/09/2015 30/09/2015
1 102 42 20/09/2015 22/09/2015
1 102 45 14/10/2015 16/10/2015
1 102 46 22/10/2015 24/10/2015
1 103 47 03/10/2015
2 101 48 27/09/2015 30/09/2015
2 102 49 27/09/2015 30/09/2015
2 103 50 28/09/2015 30/09/2015
2 104 51 28/09/2015 30/09/2015
2 201 52 29/09/2015 30/09/2015
3 101 53 26/09/2015 27/09/2015
3 102 54 29/09/2015
18 rows selected.
If we want to get only the top 8 records, we can put the previous query in an inline view, and add a condition on ROWNUM:
select x.* from ( select project_id, person_id, assignment_id, assignment_period_start, assignment_period_end from project_assignments order by project_id,person_id ) x where rownum <= 8; PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT ---------- ---------- ------------- ---------- ---------- 1 101 37 16/09/2015 18/09/2015 1 101 38 22/09/2015 24/09/2015 1 101 39 28/09/2015 30/09/2015 1 101 40 04/10/2015 06/10/2015 1 101 41 10/10/2015 12/10/2015 1 102 42 20/09/2015 22/09/2015 1 102 43 28/09/2015 30/09/2015 1 102 44 06/10/2015 08/10/2015 8 rows selected.
If we want to get only records 5-8 (or, in other words, the second page where each page contains 4 records), we’ll add yet one more inline view:
select project_id, person_id, assignment_id, assignment_period_start, assignment_period_end from ( select x.* ,rownum row_num from ( select project_id, person_id, assignment_id, assignment_period_start, assignment_period_end from project_assignments order by project_id,person_id ) x where rownum <= 8 ) where row_num > 4; PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT ---------- ---------- ------------- ---------- ---------- 1 101 41 10/10/2015 12/10/2015 1 102 42 20/09/2015 22/09/2015 1 102 43 28/09/2015 30/09/2015 1 102 44 06/10/2015 08/10/2015 4 rows selected.
A 12c Solution
The Row Limiting Clause
In Oracle 12c this can be achieved using the new Row Limiting clause, which can be added to the end of SELECT statements. The size of the page is defined by either an absolute number of records or a specific percent of records out of the complete result set. You can define whether the page starts from the first record of the original result set or from some offset. It can also be defined how to treat “ties” (i.e., when several records with the same value are on the borders of the page).
If you want to learn about many more new features of Oracle 12c, you may be interested in one of my in-house training sessions, particularly the Oracle 12c New Features for Developers full-day seminar.
Top N Queries
To get only the first 4 rows:
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
FETCH FIRST 4 ROWS ONLY;
PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT
---------- ---------- ------------- ---------- ----------
1 101 37 16/09/2015 18/09/2015
1 101 38 22/09/2015 24/09/2015
1 101 39 28/09/2015 30/09/2015
1 101 40 04/10/2015 06/10/2015
4 rows selected.
Note: the Row Limiting syntax is quite liberal. Wherever I use the ROWS keyword you can use either ROW or ROWS. Wherever I use FIRST or NEXT you can use either FIRST or NEXT.
Handling Ties
Take a look at the complete ordered result set above (the one that returned all the 18 rows of the table). You can see that the first 5 records have the same PROJET_ID (1) and PERSON_ID (101). It means that the previous query – that returned the first 4 rows – is not deterministic. It may return different 4 rows (out of 5) in different executions, because there is a tie between these 5 rows. The clause ORDER BY PROJECT_ID, PERSON_ID dictates that this set of 5 rows will be returned first, but it does not dictate the order within this set.
If we want to get a deterministic result, we can replace the keyword ONLY with WITH TIES:
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
FETCH FIRST 4 ROWS WITH TIES;
PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT
---------- ---------- ------------- ---------- ----------
1 101 37 16/09/2015 18/09/2015
1 101 38 22/09/2015 24/09/2015
1 101 39 28/09/2015 30/09/2015
1 101 40 04/10/2015 06/10/2015
1 101 41 10/10/2015 12/10/2015
5 rows selected.
FETCH FIRST 4 ROWS WITH TIES means that we get the first 4 rows, and all the successive rows that have the same values in the “ORDER BY” columns as the 4th record.
Top Percentage
We can ask for a specific percentage of the rows rather than a specific number of rows:
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
FETCH FIRST 50 PERCENT ROWS ONLY;
PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT
---------- ---------- ------------- ---------- ----------
1 101 39 28/09/2015 30/09/2015
1 101 38 22/09/2015 24/09/2015
1 101 37 16/09/2015 18/09/2015
1 101 40 04/10/2015 06/10/2015
1 101 41 10/10/2015 12/10/2015
1 102 44 06/10/2015 08/10/2015
1 102 43 28/09/2015 30/09/2015
1 102 42 20/09/2015 22/09/2015
1 102 45 14/10/2015 16/10/2015
9 rows selected.
Once again, this is not a deterministic result. To make it deterministic we use WITH TIES instead of ONLY, just as in the previous section:
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
FETCH FIRST 50 PERCENT ROWS WITH TIES;
PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT
---------- ---------- ------------- ---------- ----------
1 101 39 28/09/2015 30/09/2015
1 101 38 22/09/2015 24/09/2015
1 101 37 16/09/2015 18/09/2015
1 101 40 04/10/2015 06/10/2015
1 101 41 10/10/2015 12/10/2015
1 102 44 06/10/2015 08/10/2015
1 102 43 28/09/2015 30/09/2015
1 102 42 20/09/2015 22/09/2015
1 102 45 14/10/2015 16/10/2015
1 102 46 22/10/2015 24/10/2015
10 rows selected.
Note that we got 10 rows, which is more than the requested 50%, because the 10th row has the same PROJECT_ID and PERSON_ID as the 9th row.
Performance Considerations
Note: The percentage option means that Oracle has to count first the total number of records in the underlying result set. This can consume significant resources and time for large data sets.
Recommended Reading: Franck Patchot has recently compared (in its excellent blog) the execution plans of three alternatives of Top N queries: using ROWNUM, using ROW_NUMBER and 12c Row Limiting.
Paging
To get only the 4 rows that follow the first 4 rows (i.e., rows 5-8), we add the OFFSET clause:
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
OFFSET 4 ROWS
FETCH NEXT 4 ROWS ONLY;
PROJECT_ID PERSON_ID ASSIGNMENT_ID ASSIGNMENT ASSIGNMENT
---------- ---------- ------------- ---------- ----------
1 101 41 10/10/2015 12/10/2015
1 102 42 20/09/2015 22/09/2015
1 102 43 28/09/2015 30/09/2015
1 102 44 06/10/2015 08/10/2015
4 rows selected.
Behind the Scenes
Using the DBMS_UTILITY.EXPAND_SQL_TEXT procedure (yet another new feature of Oracle 12c), we can see that Oracle actually translates the new Row Limiting clause into conditions with analytic functions – ROW_NUMBER for the ONLY option and RANK for the WITH TIES option. Here are some examples:
var x clob
begin
dbms_utility.expand_sql_text(
input_sql_text => '
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
OFFSET 100 ROWS
FETCH NEXT 4 ROWS ONLY',
output_sql_text => :x);
end;
/
print x
-- I formatted the output to make it more readable
SELECT "A1"."PROJECT_ID" "PROJECT_ID",
"A1"."PERSON_ID" "PERSON_ID",
"A1"."ASSIGNMENT_ID" "ASSIGNMENT_ID",
"A1"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
"A1"."ASSIGNMENT_PERIOD_END" "ASSIGNMENT_PERIOD_END"
FROM (SELECT "A2"."PROJECT_ID" "PROJECT_ID",
"A2"."PERSON_ID" "PERSON_ID",
"A2"."ASSIGNMENT_ID" "ASSIGNMENT_ID",
"A2"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
"A2"."ASSIGNMENT_PERIOD_END" "ASSIGNMENT_PERIOD_END",
"A2"."PROJECT_ID" "rowlimit_$_0",
"A2"."PERSON_ID" "rowlimit_$_1",
row_number() over(ORDER BY "A2"."PROJECT_ID", "A2"."PERSON_ID") "rowlimit_$$_rownumber"
FROM "DEMO5"."PROJECT_ASSIGNMENTS""A2") "A1"
WHERE "A1"."rowlimit_$$_rownumber" <= CASE WHEN (100 >= 0) THEN floor(to_number(100)) ELSE 0 END + 4
AND "A1"."rowlimit_$$_rownumber" > 100
ORDER BY "A1"."rowlimit_$_0",
"A1"."rowlimit_$_1"
begin
dbms_utility.expand_sql_text(
input_sql_text => '
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
OFFSET 100 ROWS
FETCH NEXT 4 ROWS WITH TIES',
output_sql_text => :x);
end;
/
print x
-- I formatted the output to make it more readable
SELECT "A1"."PROJECT_ID" "PROJECT_ID",
"A1"."PERSON_ID" "PERSON_ID",
"A1"."ASSIGNMENT_ID" "ASSIGNMENT_ID",
"A1"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
"A1"."ASSIGNMENT_PERIOD_END" "ASSIGNMENT_PERIOD_END"
FROM (SELECT "A2"."PROJECT_ID" "PROJECT_ID",
"A2"."PERSON_ID" "PERSON_ID",
"A2"."ASSIGNMENT_ID" "ASSIGNMENT_ID",
"A2"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
"A2"."ASSIGNMENT_PERIOD_END" "ASSIGNMENT_PERIOD_END",
"A2"."PROJECT_ID" "rowlimit_$_0",
"A2"."PERSON_ID" "rowlimit_$_1",
row_number() over(ORDER BY "A2"."PROJECT_ID", "A2"."PERSON_ID") "rowlimit_$$_rownumber",
rank() over(ORDER BY "A2"."PROJECT_ID", "A2"."PERSON_ID") "rowlimit_$$_rank"
FROM "DEMO5"."PROJECT_ASSIGNMENTS" "A2") "A1"
WHERE "A1"."rowlimit_$$_rank" <= CASE WHEN (100 >= 0) THEN floor(to_number(100)) ELSE 0 END + 4
AND "A1"."rowlimit_$$_rownumber" > 100
ORDER BY "A1"."rowlimit_$_0",
"A1"."rowlimit_$_1"
begin
dbms_utility.expand_sql_text(
input_sql_text => '
select project_id,
person_id,
assignment_id,
assignment_period_start,
assignment_period_end
from project_assignments
order by project_id,person_id
FETCH FIRST 10 percent ROWS only',
output_sql_text => :x);
end;
/
print x
-- I formatted the output to make it more readable
SELECT "A1"."PROJECT_ID" "PROJECT_ID",
"A1"."PERSON_ID" "PERSON_ID",
"A1"."ASSIGNMENT_ID" "ASSIGNMENT_ID",
"A1"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
"A1"."ASSIGNMENT_PERIOD_END" "ASSIGNMENT_PERIOD_END"
FROM (SELECT "A2"."PROJECT_ID" "PROJECT_ID",
"A2"."PERSON_ID" "PERSON_ID",
"A2"."ASSIGNMENT_ID" "ASSIGNMENT_ID",
"A2"."ASSIGNMENT_PERIOD_START" "ASSIGNMENT_PERIOD_START",
"A2"."ASSIGNMENT_PERIOD_END" "ASSIGNMENT_PERIOD_END",
"A2"."PROJECT_ID" "rowlimit_$_0",
"A2"."PERSON_ID" "rowlimit_$_1",
row_number() over(ORDER BY "A2"."PROJECT_ID", "A2"."PERSON_ID") "rowlimit_$$_rownumber",
COUNT(*) over() "rowlimit_$$_total"
FROM "DEMO5"."PROJECT_ASSIGNMENTS" "A2") "A1"
WHERE "A1"."rowlimit_$$_rownumber" <= ceil("A1"."rowlimit_$$_total" * 10 / 100)
ORDER BY "A1"."rowlimit_$_0",
"A1"."rowlimit_$_1"
Conclusion
We saw in this part of the Write Less with More series that Row Limiting allows us to write less code in SQL.
The next post will be about another new Oracle 12c feature – SELECT FROM Package-Level Collection Types.