Allow Dynamic Setting of "Maximum Rows to Process"

joseph fuda Internal · Apr 21 2025

Idea Summary
Currently the "Maximum Rows to Process" setting for Classic and Interactive Reports only accepts static integers. I would like the ability to set the value dynamically, at runtime.

Use Cases
Give end users the ability to tweak their UI performance, e.g. use a low setting for casual browsing, change to a high setting prior to initiating a download.

Give users the ability to disable row limiting all together.

In cases where classic or interactive report results are used as input to PL/SQL processes (i.e. those that use APEX_REGION and APEX_EXEC to retrive report results sets), and these processes have dynamic rows limits, this new feature would give end users the ability to reduce the size of their input row set to avoid the PL/SQL process' row limits.

Others developers have expressed interest in this idea too.

There is this post where the user states:

“the issue is that there are times when the users will need more records than what I set as the max (100,000). Is there any way to make this setting dynamic? I'd love to add a button that removes the limit and adds a warning that they will have to wait for the results. ”

Here is another post.

Preferred Solution (Optional)
Allow the setting to accept substitution variables, similar to the way “Maximum Rows per Page” accepts Application, Page Items and System Variable substitutions.

We reviewed this idea carefully, and while it was interesting, we concluded that due to all the internal implications we need to take into account, it is unlikely to make its way into APEX.

Comments

joseph fuda OP 6 months ago
For those who might suggest adding a
```
FETCH :P1_MAX_ROWS ROWS ONLY 
```
clause to my reports as a workaround, this produces incorrect results when users leverage the Data > Sort feature in interactive reports. This is because FETCH filtering appears to happen before the user's sort settings are applied. It would need to happen after that point for the workaround to behave as needed.
carsten.czarski APEX Team OP 6 months ago
If you're indeed referring to the Maximum Rows to Process attribute, then I'm afraid the understanding of the combination with the user's sort settings is not correct.

That attribute is there to restrict the amount of rows to be sorted; so it is indeed applied before any sort happens. This attribute should always be used together with the When More Data Found message, to inform end users about the fact that data is incomplete.

You can easily try that out using the EMP table, setting a low value (e.g. “5”), and reviewing the generated query in debug output.
```
select i.*, 
       count(*) over () as APEX$TOTAL_ROW_COUNT
  from (
         select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
           from(
                 select /*+ qb_name(apex$inner) */d."EMPNO",d."ENAME",d."JOB",d."MGR",d."HIREDATE",d."SAL",d."COMM",d."DEPTNO"
                   from( select x.* from "EMP" x )d
           ) i 
       )i 
 where 1=1 
   and rownum<=6
 order by "ENAME" asc nulls last
```
In practice I often experienced that setting this value even backfires from a performance point of view, as the additional ROWNUM filter prevents the optimizer from using some better execution plans.

All in all I would not recommend to allow end users to dynamically play with these values - this is indeed a safeguard to limit the amount of rows the APEX component has to deal with - setting it will lead to incomplete results, aggregation results and sort orders are not correct - thus the importance of the message. That should always be a developer decision, and not delegated to the end user.
joseph fuda OP 6 months ago

Thank you Carsten for correcting my understanding of Maximum Rows to Process. Your explanation makes perfect sense.

It looks like I fell into the trap of describing the requirement in terms of a possible solution, instead of explaining the actual need.

I'll leave this idea as-is, since other threads have expressed an interest in it and people have already voted on it. I will, however, create a new idea that expresses my requirement for a dynamic, Top-N control in better terms and then leave it to the experts to find the best implementation. :-)

3 Voters

Allow Dynamic Setting of "Maximum Rows to Process"

Feature

Comments

Comments