Note: This previous article was enhanced with additional information regarding read-consistent pagination.
Found at the top and bottom of almost any web page that displays long lists of information are seemingly-innocuous little widgets commonly called “pagination” objects. They allow the user to advance to the next or previous page of information as well as jumping to a specific page. Although they may be a small part of a web page’s functionality, they can be a resource hog to produce if not developed with proper techniques.
Java applications seem to be a conspicuous culprit in producing poorly-conceived pagination objects. The scenario plays out in the following way:
1) Select the complete list of items to be displayed in the proper order; perhaps 1000’s of rows are selected.
2) March through the ordered list in a loop and accumulate a count of the items. When the appropriate section of the result set is found, accumulate these details into a display object.
3) Continue through the rest of the list to get full a count of the total items to feed the pagination widget.
Alternate scenarios may use two queries, one to count the total items for the entire result set and the other to get just those items to be displayed on the web page. This is an improvement over digging through the entire result set, but its still inefficient.
The appropriate solution is a Single Query. The goal is to produce a query that returns BOTH the total of count of the items in the full result set (to support creating the pagination object) and only those rows that will be displayed on the web page.
For our example, consider a table of PRODUCTS and a web page that displays any product with names based on a requested search criteria, 25 to a page. This is not an exercise in appropriate search conditions, so for our example we assume the user enters a keyword and we use % symbols on each end of the text fragment to find matching rows. The uninspired query looks like this:
SELECT product_name,
product_details
FROM products
WHERE product_name like '%Television%'
ORDER BY product_id;
The inefficient program first issues the query and then counts the rows in the result set, pulls out just the appropriate rows for display and crafts the pagination object all in a loop construct.
But the database can do most of the work for you. The following query retrieves only the rows to be displayed using in-line views and the total number of rows for the entire result set using the magic of the PARTITION BY clause.
SELECT product_name,
product_details,
total_rows
FROM (
SELECT product_name,
product_details,
total_rows,
rownum row_counter
FROM (
SELECT product_name,
product_details,
count(*) OVER () total_rows
FROM products
WHERE product_name like '%Television%'
ORDER BY product_name
)
)
WHERE row_counter between v_start_row and v_end_row;
Here’s how the query works. In the innermost in-line view, the entire result set is selected and the the count(*) OVER () column makes sure that every row contains a column with the total number of rows in the query. No matter what subset of rows are ultimately selected, the count of the entire inner result set is always available. This inner in-line view also sorts the data.
In the next in-line view, all the rows are selected from the innermost in-line view with an additional column added to identify each row by an incrementing number. Perhaps you are thinking to yourself, “Why didn’t he just add the rownum column to innermost in-line view?” The answer is that rownum is added to a result set PRIOR to an ORDER BY clause. It is necessary to first sort the result set, then add the rownum later in the process.
In the final step, our outermost query actually selects only the rows of interest by specifying BETWEEN a start and end position (provided here by the v_start_row and v_end_row variables). Most applications that use pagination probably are passed a variables for the page to display and a variable for the number of lines to display per page. In that case, the start and end positions are calculated:
v_end_row = page to display X number of rows per page
v_start_row = ((page to display - 1) X number of rows per page) + 1
The pagination object for the new page is created by capturing the total row count from any of the individual rows that come out of the query. In the loop that processes these rows, the total can be assigned to a variable for later use by the pagination constructor.
An Alternative
If you are sorting by a unique, numeric column (say a product_id), you can add use a row_number() function to create the row numbers in the innermost in-line view and simplify the query:
SELECT product_name,
product_details,
total_rows
FROM (
SELECT product_id,
product_name,
product_details,
COUNT(*) OVER () total_rows,
ROW_NUMBER() OVER (ORDER BY product_id) row_counter
FROM products
ORDER BY product_id
)
WHERE row_counter between v_start_row and v_end_row;
Preserving Read-Consistency with Pagination
If you have rapidly-changing source tables, pagination becomes more complicated since each request to the server for a different page of information will potentially yield different data sets. For example, if a page of 25 results are returned and during the time the user views the page, a new item is inserted into the database that would have appeared in position 10 on that page. When the user requests the next 25 entries, item 25 from the previous page now is shown as item 1 on the new page. The results are even worse if the old item 10 is deleted, since the item that should have been displayed as item 1 on on the new page has now dropped to item 25 of the previous page. The user never saw it! To eliminate this behavior, you can use the System Change Number and flashback query:
CREATE TABLE products (product_id NUMBER, product_name VARCHAR2(20));
INSERT INTO products VALUES (1,'Widget A');
INSERT INTO products VALUES (2,'Widget B');
INSERT INTO products VALUES (4,'Widget D');
INSERT INTO products VALUES (5,'Widget E');
COMMIT;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
58961080
SELECT product_name,
total_rows
FROM (
SELECT product_id,
product_name,
COUNT(*) OVER () total_rows,
ROW_NUMBER() OVER (ORDER BY product_id) row_counter
FROM products
ORDER BY product_id
)
WHERE row_counter between 2 and 4;
PRODUCT_NAME TOTAL_ROWS
-------------------- ----------
Widget B 4
Widget D 4
Widget E 4
INSERT INTO products VALUES (3,'Widget C');
COMMIT;
SELECT product_name,
total_rows
FROM (
SELECT product_id,
product_name,
COUNT(*) OVER () total_rows,
ROW_NUMBER() OVER (ORDER BY product_id) row_counter
FROM products as of scn 58961080 -- Flashback query!
ORDER BY product_id
)
WHERE row_counter between 2 and 4;
PRODUCT_NAME TOTAL_ROWS
-------------------- ----------
Widget B 4
Widget D 4
Widget E 4
Note that the new row for Widget C did not appear and the total count remains 4. If this query is run without the as of scn clause, the expected result set is obtained:
PRODUCT_NAME TOTAL_ROWS
-------------------- ----------
Widget B 5
Widget C 5
Widget D 5
When creating pagination web objects, simply include the system change number in the web page to enable flashback query. More information on this topic can be found in the Oracle forums here.
So there you have it. A Single Query that can dramatically shorten the amount of work for loops and cursors in web applications. As an added benefit, the bigger the initial result set, the more time savings the application will experience.
·
