Scrollable cursor support

[cemremengu]

Is there a way to create scrollable cursors like in:

https://github.com/oracle/python-cx_Oracle/blob/master/samples/ScrollableCursors.py

If not, is it planned? It would be great to have this

[cjbj]

@cemremengu there's no API exposed for this currently. It would be nice to have.

[dmcghan]

@cemremengu Could you please tell me about the use case for scrollable cursor support? I haven't yet wrapped my head around their usefulness. Perhaps I'm stuck in the typical "REST API with using a DB connection pool" model.

[cemremengu]

I was thinking about server side paging of queries (piping the results through a socket) where execution with offset-range everytime would be expensive. However, it was just a thought on top of my head and I may be very wrong 😄

[dmcghan]

@cemremengu Are you currently experiencing performance issues with pagination?

Lukas Eder talks about pagination issues here:
https://blog.jooq.org/2016/08/10/why-most-programmers-get-pagination-wrong/

He advocates keyset pagination there. I suppose at the end of the day, solutions would have to be based on the needs of the application.

I just wonder how a "scrollable" cursor helps - especially in the world of stateless architectures.

[cjbj]

One potential advantage of offset-fetch-next is that only a small rowset is constructed, which is obviously memory efficient, and also may benefit from index use. With an unbounded query (no WHERE clause), the DB will have to hold a complete set of query results open, just in case the app wants to scroll back & forth.

[cemremengu]

Thanks for the suggestions, will keep these in mind. We have some non trivial analytical queries and I am looking for ways to page them efficiently. Maybe scrollable cursors are not the most efficient way after all for such queries. Most of the clients are still on 11gR2 though so still waiting for the transition to use offset-fetch-next

[cjbj]

@cemremengu Is this in a stateless app? Once the connection is closed, you will lose access to the cursor, so scrollable cursors won't help.

I assume you want the ability to jump to arbitrary parts of the resultset?

[dmcghan]

@cemremengu

We have some non trivial analytical queries and I am looking for ways to page them efficiently.

That sounds interesting. I'm not sure the latest syntax would yield any performance benefits, but it should simplify the queries some.

Is the query time being spent on the analytics side of things or the pagination after the fact? Oracle has an arsenal of performance-enhancing features to be leveraged. If you can provide some details to the folks at Ask TOM, they might be able to advise you on what features would help you the best: https://asktom.oracle.com

[cemremengu]

It is usually on the analytics side so we don't want to run the query repeatedly and look for an easy way to browse the results.

I will fiddle around more with the current functionalities and let you know about how it turns out. For the benefit of the scrollable cursors maybe @anthony-tuininga can share some insights/cases as well with us.

[dmcghan]

so we don't want to run the query repeatedly and look for an easy way to browse the results

Ah... Can you give us an idea of the entire result set size? Maybe all of the results could be returned and pagination could be done client side? There may be some other strategies depending on the number of rows.

Also, are the results specific to a given user and various session parameters?

How often do the users leverage the pagination?

Does the pagination need to be consistent with respect to when the first page of results was returned? At what point would cached results be considered stale?

[anthony-tuininga]

@cemremengu, scrollable cursors were a lot more interesting a decade ago before stateless connections were common. I've no doubt they can still be useful but I haven't personally found much use for them. So I have no compelling cases to share with you, unfortunately! We're waiting to hear one of those from you. ;-) Since ODPI-C (on which node-oracledb is based) supports scrollable cursors it probably wouldn't take much effort to implement, but without a compelling case we'd rather spend our time on other enhancements.

[cjbj]

@cemremengu as @dmcghan indicates, you could play around with how scrollable cursor might work, and establish what the API would look like. Emulate them with a wrapper layer that fetches all (most?) results and caches them, and then lets you jump back and forth. Check slide 33 of https://static.rainfocus.com/oracle/oraclecode18/sess/1519612041174001m8Qf/PF/6-python-data-access-4485231-zhs_1526668875790001LtDU.pdf to see the controls that are used in Python cx_Oracle.

[dmcghan]

@cjbj Well, to be fair, I'm not advocating the scrollable cursor route. As @anthony-tuininga indicated, I haven't found much use for them and I'd rather see development efforts focused elsewhere until someone has a compelling use case. :)

I'm thinking more along the lines of caching, but I'm not sure how reasonable it would be just yet. For example, maybe the Query Result Cache could help.

[cemremengu]

For the record this is not an urgent or mandatory feature for me, it's just stemmed from an idea that it can be useful while I was contemplating on possible architectural solutions. I will try the suggestions proposed here but I agree that without a compelling case you should focus on other things 😄 This is better as a PR from the community.

@dmcghan We are in the process of rewriting a legacy ASP reporting application so the sluggishness may well be due to the backend technology. Size of the result set depends on the date period being queried. Queries are dynamically generated according to the user selection. However, I now think pagination is not really necessary and I will probably go with just displaying top 1000 rows and let the user export the rest. Who goes through hundreds of pages anyway..