We can create pagination in oracle itself, while querying for data. Pagination helps us to get the specific result instead of returning all the result from database.
Lets understand this using an example:
Suppose we have a table client_table which have thousands of client information, and when we query on the table it return thousands of records or all the records which match the query. By executing the below database query, you will get all the records from database table.
Lets understand this using an example:
Suppose we have a table client_table which have thousands of client information, and when we query on the table it return thousands of records or all the records which match the query. By executing the below database query, you will get all the records from database table.
e.g. SELECT * FROM client_tableBut here we don't want all the record at a time, we want the records in chunk, so we can easily investigate and understand the record and make it more readable and easier to analyse. To do so, we will optimize the same query and add the pagination into it, in pagination we will specify begin and end of row, which is actually from row number to to row number which we want to fetch.
Here is a simple example of pagination, where we are querying database to provide the range of data.
Above query will fetch the record from row number 12 to 17 and return only 6 record to you.SELECT * FROM(SELECT CT.*, rownum FROM client_table CT) rowssWHERE rowss."ROWNUM" BETWEEN 12 and 17;
References:
https://docs.oracle.com/cd/B14117_01/server.101/b10759/pseudocolumns008.htm
https://stackoverflow.com/questions/4552769/sql-rownum-how-to-return-rows-between-a-specific-range
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/select/first-n-rows/index