Tip:
Highlight text to annotate it
X
Hi, this is Tom Kyte and I am here with Maitreyee Chaliha and
today we are gonna be talking about easier Top N and Pagination queries from the
database. I have written no less than 3 articles
in Oracle magazine describing how to do top N and pagination queries,
getting the first 10 records after sorting data or
getting rows 100 through 120 from a resultset. I had to write so many articles because frankly,
its been counter intituitive as to how to do it in the past and
there's actually multiple ways to do it. You could use row num, row_number and so on.
So it was not intituitive and there were many different ways you could
implement it, it was a little bit hard to do.
Now with the new row_limiting_clause syntax inside the database it becomes rather trivial.
You are basically going to say run this query and then you have a row limit
clause in the bottom that says I'd like you to
fetch rows 1 through 10 and then stop. That tells us to get the first 10 records.
Or you could say fetch rows 100 through 120, you'd basically be getting page 6
out of a report that pages 20 rows at a time. So this easy top n row limiting clause makes
what used to be hard, very easy. And again it's also a migration detail as
well because some other databases have implemented this
row_limiting _clause in the past and so this makes it easier to
move queries from say MYSql to an Oracle Databse or vice-versa.
Ok, I am going to turn it over to Maitreyee now.
The new row_limiting_clause allows you to limit the rows returned by the query.
We can specify an offset, and number or percentage of rows to return.
We will be using few examples for demonstration. The FETCH clause is used to specify
the number of rows or percentage of rows to return.
For example, fetching the first five records from a table.
This statement returned the 5 employees with the lowest employee_id values.
The OFFSET clause is used to specify the number of rows to skip before row limiting
begins. If OFFSET is not specified then
row limiting begins with the first row. Now, to return the next 5 records
from the same table we will use OFFSET with FETCH and NEXT.
This statement returned the next 5 employees with the lowest employee_id values
The following statement returned the 5 percent of employees with the lowest
salaries PERCENT is used to specify
the percentage of the total number of selected rows to return.
WITH TIES is specified to return additional rows with the same sort key
as the last row fetched. If we specify WITH TIES,
then we must specify the order_by_clause, otherwise no additional rows will be returned.
Here, because WITH TIES is specified, the statement returned the 5 percent
of employees with the lowest salaries, plus all additional employees with
the same salary as the last row fetched. Thank you for watching this demonstration
on easy Top N and Pagination Query using row_limiting_clause.
We hope you've found this video useful. For more information please
visit the Oracle Database 12c online library.