Possible inconsistency with SQLPaging on Oracle

[lextra]

Hello,

We are still working on the grid using a datasource has a backend with the progressive loading pattern and the SQL paging in order to let users scroll data coming from a huge table.

The generated SELECT statement may cause inconsistency between different fetchData() invoked from scrolling (we are using SQLPaging=SQL_LIMIT):

 SELECT * FROM (SELECT /*+ FIRST_ROWS(76) */ a.*, rownum myrownum FROM (SELECT T_HUGE_TABLE.T_DT, T_HUGE_TABLE.INSTANCE, T_HUGE_TABLE.INTERNAL_ORDER_ID, T_HUGE_TABLE.SIDE, T_HUGE_TABLE.ISIN FROM T_HUGE_TABLE) a where rownum <=76) WHERE myrownum >= 1

Oracle do not ensure that two different SELECT * FROM t_huge_table; will return the lines in the same order. As the ROWNUM clause is evaluated after that, we may display the same lines.

A solution can be to add a $defaultOrderByClause which order by every columns, but it may cause poor performances (but it should be okay if a WHERE clause exists to limit the number of retrieved rows).

Please note that the same issue may occur with the JDBC_SCROLL as multiple executions of the same SELECT statement are done.

Do you have any feedback on this situation?

Thanks,
A.

[Isomorphic]

In general, DBs do not guarantee a stable return order in the absence of ORDER BY, but the observed behavior is that the return order is stable unless changes are made, and often stable even then.

If changes are made and you have not put in place a mechanism to tell the client to invalidateCache, this can cause client and server row numbering to get out of sync even though the server is consistent about row order from fetch to fetch.

The effect of this is just that when paging through data a row may either never be loaded or be loaded twice - at least until filter criteria or sort direction is changed causing a re-fetch.

If you have not tried to address the latter, far more likely case of row numbering inconsistency (which most applications do not), we wouldn't worry about this much rarer and more theoretical row consistency issue with sqlLimit. But if you *are* worried about it, a simple fix is just to provide an initial sort direction so you aren't relying on the DB's natural order.