APEX

I have a report region whose datasource is based on a select statement that retrieves data from a nested table data collection. I'm using this approach so that users can query on data that will eventually appear on a bill, but without writing to the physical table that will be used to create the bill.

The query looks like this: (Note the following: the package maintaining the collection is: bill_fac_run_time_maint, the function returning the collection is: bill_fac_runtime(), and the nested table type is: bfrttypeset)

SELECT facid, facility, str1, str2, city_state_zip, categ_type, ea_code, vcc,
actor_id, address_id, mm_addr_id
FROM TABLE
(CAST
(bill_fac_run_time_maint.bill_fac_runtime () AS bfrttypeset
)
) bft

The package: bill_fac_run_time_maint has a an initialization procedure which simply instantiates the collection to a set of records (approx. 9700) returned from a query on a view. I put it into the initialization section so the collection would be built ONCE per session when a package component is first referenced.

Initial queries in SQL*Plus and Oracle Forms take approximately 1 minute, but subsequent queries return data instantaneously because the collection is in memory.

My problem in APEX, however, is that, in addition to the first query being slow as in SQL*Plus, all subsequent page refreshes (i.e. every time I click for the next 15 (standard report region page)) are also slow. What's also disturbing is that you have no indication that anything is going on and suddenly the next batch of records appears.

The one hint that I have that it may be a pagination issue is the bottom message I got when I first clicked on the "next" button: javascript:$a_report('99999999999','16','15','15')

This leads me to believe that its requesting the next 15 records starting from row 16, but how does APEX know where it left off?

I just tried running it in APEX's SQL Workshop and subsequent queries also took rather long.

This is confusing because that collection should be available for the duration of a given session.

Any ideas would be greatly appreciated.

Bill