Paging with Oracle
Something like this should work: From Frans Bouma's Blog
SELECT * FROM
(
SELECT a.*, rownum r__
FROM
(
SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
ORDER BY OrderDate DESC, ShippingDate DESC
) a
WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)
Best practice for pagination in Oracle?
If you're already using analytics (ROW_NUMBER() OVER ...) then adding another analytic function on the same partitioning will add a negligible cost to the query.
On the other hand, there are many other ways to do pagination, one of them using rownum:
SELECT *
FROM (SELECT A.*, rownum rn
FROM (SELECT *
FROM your_table
ORDER BY col) A
WHERE rownum <= :Y)
WHERE rn >= :X
This method will be superior if you have an appropriate index on the ordering column. In this case, it might be more efficient to use two queries (one for the total number of rows, one for the result).
Both methods are appropriate but in general if you want both the number of rows and a pagination set then using analytics is more efficient because you only query the rows once.
How to correct pagination in oracle?
This is explained in the The Underground PHP and Oracle Manual, page 181:
The canonical paging query for Oracle8i onwards is given on http://asktom.oracle.com:
select *
from ( select a.*, rownum as rnum
from (YOUR_QUERY_GOES_HERE -- including the order by) a
where rownum <= MAX_ROW )
where rnum >= MIN_ROW
Here, MIN_ROW is the row number of
first row and MAX_ROW is the row number of the last row to return.
There is even a full example script:
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$mystmt = "select city from locations order by city";
$minrow = 4; // row number of first row to return
$maxrow = 8; // row number of last row to return
$pagesql = "select *
from ( select a.*, rownum as rnum
from ( $mystmt ) a
where rownum <= :maxrow)
where rnum >= :minrow";
$s = oci_parse($c, $pagesql);
oci_bind_by_name($s, ":maxrow", $maxrow);
oci_bind_by_name($s, ":minrow", $minrow);
oci_execute($s);
oci_fetch_all($s, $res);
var_dump($res);
?>
Edit:
As mentioned by Christopher Jones, in Oracle >=12.1 there is a cleaner way to do this using the offset clause:
select *
from mytable
order by myfield
offset X rows fetch next Y rows only
Oracle Pagination strategy
Pagination pattern has been invented for the purpose of websites presentation (in opposite to scrolling navigation), and works best there. In short, the live user is practically unable to view thousands/millions of records at once, so the information is divided into short pages (50~200 records), where one query is usually sent to the database for each page. The user usually clicks on a few pages only, but does not browse all of them, in addition the user needs a bit of time to browse the page, so the queries are not sent to the database one by one, but in long intervals. The time to retrieve a chunk of data is much shorter than retrieving all millions of record, so the user is happy because he does not have to wait long for subsequent pages, and the overall system load is smaller.
But it seems from the question that the nature of your application is oriented to batch processing rather than to the web presentation. The application must fetch all records and do some operations/transformations (calculations) on each of the records. In this case , completely different design patterns are used (stream/pipelined processing, sequence of steps, parallel steps/operations etc), and pagination will not work, if you go that way you will kill your system performance.
Instead of fancy theory, let's look at simple and practical example which will show you what differences in speed we are talking here
Let say there is a table PAGINATION with about 7 millions of records:
create table pagination as
select sysdate - 200 * dbms_random.value As my_date, t.*
from (
select o.* from all_objects o
cross join (select * from dual connect by level <= 100)
fetch first 10000000 rows only
) t;
select count(*) from pagination;
COUNT(*)
----------
7369600
Let say there is an index created on MY_DATE column, and index statistics are fresh:
create index PAGINATION_IX on pagination( my_date );
BEGIN dbms_stats.gather_table_stats( 'TEST', 'PAGINATION', method_opt => 'FOR ALL COLUMNS' ); END;
/
Let say that we are going to process about 10% of records from the table between the below dates:
select count(*) from pagination
where my_date between date '2017-10-01' and '2017-10-21';
COUNT(*)
----------
736341
and finally let say that our "processing" for simplicity, will consist in simple summing of lengths of one of field.
This is a simple paging implementation:
public class Pagination {
public static class RecordPojo {
Date myDate;
String objectName;
public Date getMyDate() {
return myDate;
}
public RecordPojo setMyDate(Date myDate) {
this.myDate = myDate;
return this;
}
public String getObjectName() {
return objectName;
}
public RecordPojo setObjectName(String objectName) {
this.objectName = objectName;
return this;
}
};
static class MyPaginator{
private Connection conn;
private int pageSize;
private int currentPage = 0;
public MyPaginator( Connection conn, int pageSize ) {
this.conn = conn;
this.pageSize = pageSize;
}
static final String QUERY = ""
+ "SELECT my_date, object_name FROM pagination "
+ "WHERE my_date between date '2017-10-01' and '2017-10-21' "
+ "ORDER BY my_date "
+ "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
List<RecordPojo> getNextPage() {
List<RecordPojo> list = new ArrayList<>();
ResultSet rs = null;
try( PreparedStatement ps = conn.prepareStatement(QUERY);) {
ps.setInt(1, pageSize * currentPage++ );
ps.setInt(2, pageSize);
rs = ps.executeQuery();
while( rs.next()) {
list.add( new RecordPojo().setMyDate(rs.getDate(1)).setObjectName(rs.getString(2)));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try{rs.close();}catch(Exception e) {}
}
return list;
}
public int getCurrentPage() {
return currentPage;
}
}
public static void main(String ...x) throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setURL("jdbc:oracle:thin:test/test@//localhost:1521/orcl");
long startTime = System.currentTimeMillis();
long value = 0;
int pageSize = 1000;
try( Connection conn = ds.getConnection();){
MyPaginator p = new MyPaginator(conn, pageSize);
List<RecordPojo> list;
while( ( list = p.getNextPage()).size() > 0 ) {
value += list.stream().map( y -> y.getObjectName().length()).mapToLong(Integer::longValue).sum();
System.out.println("Page: " + p.getCurrentPage());
}
System.out.format("==================\nValue = %d, Pages = %d, time = %d seconds", value, p.getCurrentPage(), (System.currentTimeMillis() - startTime)/1000);
}
}
}
A result is:
Value = 18312338, Pages = 738, time = 2216 seconds
Now let's test a very simple stream based solution - just take only one record, process it, discard it (freeing up memory), and take the next one.
public class NoPagination {
static final String QUERY = ""
+ "SELECT my_date, object_name FROM pagination "
+ "WHERE my_date between date '2017-10-01' and '2017-10-21' "
+ "ORDER BY my_date ";
public static void main(String[] args) throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setURL("jdbc:oracle:thin:test/test@//localhost:1521/orcl");
long startTime = System.currentTimeMillis();
long count = 0;
ResultSet rs = null;
PreparedStatement ps = null;
try( Connection conn = ds.getConnection();){
ps = conn.prepareStatement(QUERY);
rs = ps.executeQuery();
while( rs.next()) {
// processing
RecordPojo r = new RecordPojo().setMyDate(rs.getDate(1)).setObjectName(rs.getString(2));
count+=r.getObjectName().length();
}
System.out.format("==================\nValue = %d, time = %d seconds", count, (System.currentTimeMillis() - startTime)/1000);
}finally {
try { rs.close();}catch(Exception e) {}
try { ps.close();}catch(Exception e) {}
}
}
A result is:
Value = 18312328, time = 11 seconds
Yes - 2216 seconds / 11 seconds = 201 times faster - 20 100 % faster !!!
Unbelievable ? You can test it yourself.
This example shows how important it is to choose the right solution (right design patterns) to solve the problem.
Alternatives to LIMIT and OFFSET for paging in Oracle
You will need to use the rownum pseudocolumn to limit results. See here:
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
Is order by mandatory for pagination in oracle?
1) Is order by mandatory with OFFSET n ROWS FETCH NEXT m ROWS ONLY?
Syntactically not, semantically it is!
Reason: if you don't add an ORDER BY clause, the database may return the orders in any order. Now if you execute the query first for the first page, you'll get them in any order. The next time you execute the query to fetch the next page may return the orders in any other row.
Therefore you need on ORDER BY clause that establishes a definite order of rows (so that no row is a peer with another row). In practice, you should always include something unique/primary key in the ORDER BY clause to be on the safe side. (you can still use non-unique in the ORDER BY clause — even as leading columns).
e.g.
ORDER BY time_stamp DESC, id DESC
This is a logical requirement for all types of pagination that execute separate queries for each page.
2) Is order by mandatory with RowNum?
Yes, see above.
3) How to create pagination in above sql query using RowNum?
Neither OFFSET nor ROWNUM alone are good enough to implement stable pagination.
Think about this: What if a new row is inserted after you have fetched the first page and before you fetch the second page?
There is another way to implement stable pagination called key-set pagination.
The main idea is not to skip "seen rows" by telling the database how many rows to skip and hoping no rows were added in the meanwhile, but to use a unique identification of which rows have already been seen and which not.
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLY
Remember that you need an ORDER BY that establishes a definitie order anyway. You can use these columns to pinpoint the place until where you have received the data before.
Read more about this method at my website:
http://use-the-index-luke.com/no-offset
oracle sql pagination with total pages or total entries
I would use analytic functions to do the work you're trying to do, e.g.:
SELECT res.*,
CEIL(total_num_rows/pagesize) total_num_pages
FROM (SELECT o.*,
row_number() OVER (ORDER BY orderdate DESC, shippingdate DESC) rn,
COUNT(*) OVER () total_num_rows
FROM orders o
WHERE customerid LIKE 'A%') res
WHERE rn BETWEEN (pagenumber - 1) * pagesize + 1 AND pagenumber * pagesize;
N.B. untested.
The query does the pagination by using the row_number() analytic function to assign a number to each row, across the specified groups and in the specified order. Since we don't have a partition by clause in the OVER section, the function is working across the entire set of rows.
Similarly, we use the count() analytic function to get the count of the rows across the specified groups - and again, as we haven't specified any partition by clause, the function works across the entire set of rows.
Now you have those numbers, it's a simple matter to do the pagination and find the total number of pages.
How ROWNUM works in pagination query?
You have 4 questions, and all revolve around the usage and functionality of ROWNUM. I will answer each question one-by-one.
Why (this was my first attempt until I search on SO) Select * From Person Where rownum > 100 and rownum < 110; returns 0 rows ?
Nice explanation by Thomas Kyte regarding ROWNUM and pagination here.
A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:
select *
from t
where ROWNUM > 1;
Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1.
Why there is no simple way to do something like Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound ?
Yes, there is. From Oracle 12c onwards, you could use the new Top-n Row limiting feature. See my answer here.
For example, the below query would return the employees between 4th highest till 7th highest salaries in ascending order:
SQL> SELECT empno, sal
2 FROM emp
3 ORDER BY sal
4 OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
EMPNO SAL
---------- ----------
7654 1250
7934 1300
7844 1500
7499 1600
SQL>
How to get rid of the r column in the resulting values?
Instead of select *, list the required column names in the outer query. For frequently using the query, creating a view is a simple one time activity.
Alternatively, in SQL*Plus you could use the NOPRINT command. It will not display the column name you don't want to display. However, it would only work in SQL*Plus.
For example,
COLUMN column_name NOPRINT
For example,
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> COLUMN dname NOPRINT
SQL> COLUMN LOC NOPRINT
SQL> SELECT * FROM dept;
DEPTNO
----------
10
20
30
40
SQL>
Does it ensure correct pagination?
Yes, if you write the pagination query correctly.
For example,
SELECT val
FROM (SELECT val, rownum AS rnum
FROM (SELECT val
FROM t
ORDER BY val)
WHERE rownum <= 8)
WHERE rnum >= 5;
VAL
----------
3
3
4
4
4 rows selected.
SQL>
Or, use the new row limiting feature on 12c as I have shown above.
Few good examples here.
Related Topics
MySQL Error:: 'Access Denied For User 'Root'@'Localhost'
Auto Increment After Delete in MySQL
SQL Statement Is Ignoring Where Parameter
Error 1452: Cannot Add or Update a Child Row: a Foreign Key Constraint Fails
When Should I Use Cross Apply Over Inner Join
Entity Attribute Value Database Vs. Strict Relational Model Ecommerce
Equivalent of Limit and Offset For SQL Server
Oracle: How to Upsert (Update or Insert into a Table)
Can You Use an Alias in the Where Clause in MySQL
MySQL Query Group by Day/Month/Year
MySQL Error: Key Specification Without a Key Length
How to Combine Multiple Rows into a Comma-Delimited List in Oracle
Index For Finding an Element in a Json Array