Skip to Main Content

Max Row Count

The apex_collection.create_collection_from_query_b procedure has a p_max_row_count parameter (in newer Apex versions anyway).
This can come in handy if you want to limit the number of rows that are put into the collection. If you're dealing with very large tables this can cause performance issues.

It does have a drawback however, which I ran into recently when I wanted to raise an exception (to tell the user to apply more filters) whenever more than X rows were queried.
The p_max_row_count parameter works fine for that, but I also needed an "order by" in the query to sort the result set. For large result sets, this slows things down quite a bit.
You could use apex_collection.sort_members for that (and remove the "order by" from the query), but that procedure turned out to be quite slow and also it is limited to 1 column.

The resulting code looks something like the example below.
# 1 : Query with "order by" and using p_max_row_count. Slow because the query sorts ALL rows before create_collection_from_query_b gets the subset of l_max_row_count rows.
# 2 : Query with a rownum limit and "order by", and NOT using p_max_row_count. Faster because the query only gets the subset of l_max_row_count rows and then sorts only that.