ADF 12.2.1 ADF BC REST service is compatible with range paging mode of ADF BC View Object. Range paging generates SQL query with rownum to retrieve only a subset of rows to be returned to the REST service. This can be especially effective when executing SQL query against large tables. Range paging size is determined dynamically by parameter in REST URL.
When range size is not specified, ADF BC returns 5 rows. This number represents iterator range size configured by default and it can be changed in iterator binding (the one generated for REST service). In the example below, I have changed returned rows size to 10. Parameter limit sets how many rows will be returned in one call:
Instead of default 5 rows, 10 rows will be returned now. This is visible in the log, View Object is updated based on the new range size (changing iterator range size from :5 to :11). SQL query is executed and 10 rows are fetched (similar principle as with ADF Faces table). No range paging is configured for the VO yet::
Let's change VO to work in Range Paging mode. No need to set Range Size, it will be updated automatically based on the number of rows fetched by REST service:
Same 10 rows are being fetched, SQL query is updated to fetch with rownum. It fetches a range of rows required to fill requested range by REST:
Now let's fetch second page of data. This can be done using another parameter - offset. Change offset to 1 * limit, this will fetch second page of data:
This time ADF BC executes two range paging queries. One for first page and another for second. Results from second query are returned to REST service. The same principal works with ADF Faces table. While this is still better than running VO without range paging, but there should be no query executed for the first page. We are going to log a bug for this with Oracle Support:
Download sample application - ADFBCRestApp_v3.zip. Check my previous post about CRUD support by ADF BC REST - CRUD with ADF 12.2.1 ADF BC REST.
When range size is not specified, ADF BC returns 5 rows. This number represents iterator range size configured by default and it can be changed in iterator binding (the one generated for REST service). In the example below, I have changed returned rows size to 10. Parameter limit sets how many rows will be returned in one call:
Instead of default 5 rows, 10 rows will be returned now. This is visible in the log, View Object is updated based on the new range size (changing iterator range size from :5 to :11). SQL query is executed and 10 rows are fetched (similar principle as with ADF Faces table). No range paging is configured for the VO yet::
Let's change VO to work in Range Paging mode. No need to set Range Size, it will be updated automatically based on the number of rows fetched by REST service:
Same 10 rows are being fetched, SQL query is updated to fetch with rownum. It fetches a range of rows required to fill requested range by REST:
Now let's fetch second page of data. This can be done using another parameter - offset. Change offset to 1 * limit, this will fetch second page of data:
This time ADF BC executes two range paging queries. One for first page and another for second. Results from second query are returned to REST service. The same principal works with ADF Faces table. While this is still better than running VO without range paging, but there should be no query executed for the first page. We are going to log a bug for this with Oracle Support:
Download sample application - ADFBCRestApp_v3.zip. Check my previous post about CRUD support by ADF BC REST - CRUD with ADF 12.2.1 ADF BC REST.
Andrejus, I am not sure the 1 ist for pages but row number. Otherwise an offset of 1 to 5 doesn't make sense. REST also doesn't know of "pages"
ReplyDeleteHi Frank,
ReplyDeleteI'm talking about virtual pages and combination of limit/offset parameters allows to simulate page of rows. This is useful for JET table with pagination, instead of fetching all rows, we could fetch only one page (10 rows) at a time.
If we assume, there are 10 rows in one table page, first 10 rows can be retrieved with limit = 10 and offset = 0 (starting from first row). Second 10 rows (second page) can be retrieved with limit = 10 and offset = 1 * limit = 10 (this will shift first row in the result set after row Nr. 10 from first page). Third page can be retrieved with limit = 10 and offset = 2 * limit = 20, etc.
VO pagination is correctly calculated based on limit parameter, however it doesn't take into account offset value. With offset = 10, REST response displays rows starting from row Nr. 11, but VO is fetching all 10 rows before row Nr. 11 without any use.
Regards,
Andrejus