It seems to me that Elasticsearch scroll means return a cursor - a collection of rows that you iterate over, and you may not read all of them. This is the default operation of JDBC.
So, I guess we need to give the user a way to signal their intent to read all rows versus only the first few. Oracle’s FIRST_ROWS and ALL_ROWS hints[1] seem close to this. We would want the hints to be acted upon by both the optimizer and the JDBC transport. Related is pagination. SQL has FETCH and OFFSET, which allow you to retrieve different pieces of a large result set in separate statements or (using query parameters) executions. It would be useful if the server could be given a hint to cache a statement across page requests. Julian [1] https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#4924 > On Oct 24, 2018, at 11:19 AM, Christian Beikov <christian.bei...@gmail.com> > wrote: > > Hey, > > not sure if this should be an SQL keyword. JDBC specifies various constants > that can be used at statement creation time: > https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html > > Not sure though if or how these configurations are accessible for data stores > or dialects, but IMO using these would be the proper way. > > Regards > > Christian > >> Am 24.10.2018 um 18:44 schrieb Andrei Sereda: >> Hello, >> >> >> I was thinking about adding [scrolling functionality]( >> https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-scroll.html) >> to elastic search adapter. Since scrolling has non-negligible effect on the >> cluster it should be selectively enabled on per query basis. So, likely, >> user has to explicitly set "scroll flag" somewhere. >> >> Most natural way seems in SQL. [Calcite sql grammar]( >> https://calcite.apache.org/docs/reference.html) has `SCROLL` keyword >> (unused to my knowledge). There were also discussions about adding hints to >> Calcite. >> >> ### Examples >> ```sql >> -- special sql keyword ? >> SCROLL select * from elastic; >> >> -- assuming hints are available in calcite >> /* HINT: scroll */ select * from elastic; >> ``` >> >> What people think about this use-case ? Are there better ideas ? >> >> Regards, >> Andrei. >>
