X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Application Express 18 and Report Pagination

Carsten Czarski
Consulting Member of technical Staff

Starting with APEX 18.1, some significant changes where introduced on how APEX components (report, charts, calendar and others) get data. In earlier versions, each component generated their own SQL query. That was fine as long as APEX only worked with data in the local database.

However, with the requirement to support external data sources like REST (Web Source Modules) or REST Enabled SQL, components cannot generate their own SQL any more. An example: End users view an Interactive Report and enter search filters. When that interactive report is based on a REST service, how to build a SQL query to execute the end user filters?

So, starting with APEX 18.1, all components get their data from a central infrastructure. Developers using the APEX_EXEC package, are using that infrastructure as well. The component does a "get data" request, together with filters, the requested page, aggregations and so on. The data infrastructure generates a SQL query for the local database or a REST Enabled SQL service and generates HTTP requests for REST services. Dependent on the actual data source, filters or pagination, different requests are generated.

This also affects how report pagination works. Before APEX 18.1, each report component (Classic Report, Interactive Report, Interactive Grid) did pagination individually. Classic and Interactive Reports did pagination while fetching data from the cursor:

  • The cursor was opened and fetching always started at the first row.
  • Rows were fetched and skipped until the current page was reached.
  • Rows were fetched and displayed for the current page
  • After the last row of the current page was displayed, fetched stopped and the cursor was closed.

Interactive Grid (as a more recent component) implements filtering at the SQL level. Using the ROWNUM pseudo column, Interactive Grid only selects the rows which are needed to display the current view.

Skipping unneeded rows cannot be done for reports on REST services or REST Enabled SQL. That would lead to unnecessary HTTP requests being made and unnecessary JSON being parsed. So, APEX 18.1 not only unifies data access for APEX components, it also unifies how components do pagination. Interactive Grid does will use the new data infrastructure when external data sources are supported. Thus, the following explanations will not apply for Interactive Grid today. 

To see the new pagination implementation in action, let's have a look at a classic report example. Consider a classic report with a SQL query emulating 100,000 rows.

Run that report and advance a few pages, e.g. until you see the following picture.

To get this view, Application Express had to actually discard the first 30 rows and to stop fetching after row number 40. Before APEX 18.1, the report always started to fetch at the first row - then 30 rows were discarded. After row #40, APEX simply stopped fetching and closed the cursor.

APEX 18 and higher work different - all pagination is now done with the SQL query: APEX extends the report query in order to fetch only relevant rows - you can see the actual SQL query being executed by turning on debug mode at level 9.

Then review debug output - you will need to scroll down a bit ... 

APEX uses the ROW_NUMBER analytic function to select the relevant rows for the current report page. Rows for previous pages are not even selected, and the cursor does not even provide rows to display after the current page. 

As an interesting side note: Level 9 Debug output will also contain the actual execution plan for the SQL query. APEX retrieves that immediately after execution.

Once you enable report features, the SQL query will change. For instance, the Compute Sum feature for a classic report column will lead to the SUM analytic function being added to the report query.

APEX 18.2 allows the developer to control, how pagination is executed: APEX provides two "pseudo hints": APEX$USE_ROWNUM_PAGINATION and APEX$USE_NO_PAGINATION. These pseudo hints are only recognized by APEX and must be placed into the Optimizer Hint region attribute in page designer. Adding them to the SQL query text will not work. The following screen shot illustrates this.

The report results are exactly the same. However, now APEX will use the "classic" ROWNUM pseudocolumn instead of the ROW_NUMBER analytic function in order to select the relevant rows. The actually executed SQL query looks as follows:

Another pagination implementation is APEX$USE_NO_PAGINATION. Again, provide this hint as the Optimizer Hint region attribute.

This hint will bring the behavior from APEX versions earlier than 18.1 back. No pagination filters will be added to the report SQL. In the debug output, the query will now look as follows:

The query within debug output clearly shows that no ROWNUM or ROW_NUMBER filters are used any more. Further below, debug output shows that APEX now (as it did in 5.1 and earlier) starts fetching at the first row and skips rows until the rows of interest have been reached.

Now comes the obvious question: What are these hints good for ..? 

Well, the default pagination implementation is the ROW_NUMBER analytic function. That makes sure that only the actually displayed rows are fetched from the database. The SQL row limiting clauses OFFSET and LIMIT, introduced with Oracle12c, also use ROW_NUMBER behind the scenes. The default should be the best solution in the majority of cases.

However, cases have been observed (especially with complex SQL queries, selecting from complex views), where the usage of ROW_NUMBER led to bad query execution plans - and the ROWNUM implementation would have been the better choice. The APEX$USE_ROWNUM_PAGINATION pseudo hint helps in these situation. And when the behavior of APEX 5.1 and earlier is the best solution (which was to append no pagination filters at all), the APEX$USE_NO_PAGINATION hint brings exactly that back.