Thursday, October 21, 2010

APEX Reports: No Limit Downloads

When configuring a standard report in APEX you can define the maximum number of rows that the query will return. This setting is called Max Row Count and can be configured in the Report Attributes tab. If you leave it blank it will default to 500 rows.


It is recommended that you set the Max Row Count with a low value. Using a small number will help improve performance since it reduces the amount rows APEX must count in order to display the pagination information. On a business perspective, it may not make sense to return thousands of rows for a user to view online.

What if the user wants to retrieve all the data when they download the report? From the end users perspective this makes sense since they may want to extract all the data to do some custom analysis.

My first thought was to use a page item for the Maximum Row Count. I quickly discovered that it only takes a numeric value. As a work around you can control the rows returned directly from the SQL statement. Here's an example of this:

- Create a large table:

CREATE TABLE large_emp
AS
SELECT *
FROM emp
CONNECT BY LEVEL <= 5;
- Create standard report
Create a standard report using the following query. Set the Max Row Count to 999999999 (i.e. some very large number)

SELECT ename,
job,
sal,
comm
FROM large_emp
WHERE ROWNUM <= CASE WHEN :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN ROWNUM ELSE 500 END;
You are manually defining the Max Row Count by adding in ROWNUM predicate.

When users views the report in APEX it will only display a maximum of 500 rows. When they download the report the csv file will contain all the rows.

A caveat with this approach is that if your report has more than 500 rows you won't see the "... of more than 500" rows message as part of the pagination. Instead users will see "rows ... of 500" in the pagination. Users may be misled to think that the report only has 500 rows of data.

When I reviewed this technique I was concerned about performance issues when viewing the report in APEX. Here are the TKPROF outputs of the original query (with Max Row Count = 500) and the alternate query with the ROWNUM predicate (Max Row Count = 999999999):


-- Both outputs are for VIEWING the report (not downloading it)
--
-- Original Query
-- Max Row Count = 500
-- Run for displaying rows 1~15
SELECT ename, job, sal, comm
FROM large_emp
order by 3 -- Automatically added by APEX

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 501 0.65 1.42 14 3417 3 501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 504 0.65 1.42 14 3418 3 501

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40 (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
501 SORT ORDER BY (cr=3417 pr=14 pw=2053 time=1417580 us)
579194 TABLE ACCESS FULL LARGE_EMP (cr=3417 pr=0 pw=0 time=1158549 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write temp 70 0.00 0.00
direct path read temp 2 0.00 0.00


-- *************************************

-- New Query
-- Max Row Count = 9999999999
-- Run for displaying rows 1~15
SELECT ename, job, sal, comm
FROM large_emp
WHERE ROWNUM <= CASE WHEN :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN ROWNUM ELSE 500 END
order by 3 -- Automatically added by APEX

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 1 0
Fetch 501 0.38 0.53 0 3417 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 505 0.38 0.54 0 3417 1 500

Misses in library cache during parse: 1
Parsing user id: 40 (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
500 SORT ORDER BY (cr=3417 pr=0 pw=0 time=535125 us)
500 COUNT (cr=3417 pr=0 pw=0 time=3376 us)
500 FILTER (cr=3417 pr=0 pw=0 time=2298 us)
579194 TABLE ACCESS FULL LARGE_EMP (cr=3417 pr=0 pw=0 time=5212959 us)
I'm not a performance guru and I can't comment too much on these outputs. If you think this will have negative effects on performance please add your feedback as a comment on this post.

4 comments:

  1. A friend of mine pointed out that I have a flaw in my logic since the rownum will be processed before the order by and therefor the dataset won't be correct. This test case highlights this issue:

    create table data as
    select level as column1
    from dual
    connect by level <= 10
    order by dbms_random.value;

    select column1
    from data
    where rownum <= 5
    order by 1;

    select *
    from (
    select column1
    from data
    order by 1)
    where rownum <= 5;

    ReplyDelete
  2. Brilliant! Just what I needed, and now users can download CSV reports with all rows without me having to mess with any print report attributes or configuring a print server. THANKS!

    ReplyDelete
  3. Hi Martin - did you come up with a solution to work around the order by issue you mentioned above?

    Thanks,

    Mike

    ReplyDelete
    Replies
    1. Just wrap the query in another select statement and apply the rownum predicate there (demoed in my previous comment. Hope this helps.

      Delete