Re: Does derby support "server-side cursors" in embedded mode?
Some responses inline...
On 11/7/16, 7:16 AM, Johann Petrak wrote:
> What I would like to do is this: use Derby in embedded mode to create a
> very large table with fields "key" and "value":
> create table mytable (
> key varchar(100) not null,
> value varchar not null)
> and a non-unique index on field key.
> This table would get filled with about 100 million rows where each value
> could be several thousand characters.
> Then, I would need to query the database to select all rows in order
> of keys,
> like this:
> SELECT key,value from tab order by key
> The result set would get processed by repeatedly calling next.
> Now here are my questions:
> * can derby do this without putting the complete result into memory
> first (like other embedded Java databases do)?
Since you are creating an index on the ORDER BY column, I would expect
that a dataset of this size would cause Derby to read the index from
start to finish, probing, as it goes, into the heap to retrieve the
value column. I would not expect that Derby would toss any rows into a
sorter. You might fill up Derby's page cache, but you would not need to
throw extra heap at Derby in order to get this experiment to succeed.
> * Can derby deal with the statement.setFetchSize(n) method to actually
> limit the number of rows getting fetched at once?
The embedded Derby JDBC driver implements both Statement.setFetchSize()
Hope this helps,
> My main concern is if it is possible to deal with the result set of
> that query, where I have 100 million rows, and
> each row has considerable size, without needing a lot of heap space or
> RAM: ideally I would only need the RAM needed
> to hold a couple of hundred or thousand result rows in memory.
> Thanks a lot!