Overview of result sets |V2.4.2|OceanBase Connector/J| docs|Distributed Database

Overview of result sets

Last Updated：2023-07-27 06:42:08 Updated

Result sets can be categorized into full result sets, streaming result sets, and cursor result sets by data read method.

The following table describes the three result set categories, including their advantages and disadvantages.

Result set category	Query method	Data reading method	Advantages	Disadvantages
Full result set	Ordinary query	All results are read at a time.	The application code is simple. When the data volume is small, the result set is read in the fastest speed.	The out-of-memory (`OOM`) error will be reported when the data volume is large.
Streaming result set	Streaming query	Data is read from the socket row by row rather than all at a time.	The `OOM` error will not occur even when the data volume is large.	The execution occupies the database for a longer time and cannot be stopped once it starts. Therefore, it may cause network congestion.
Cursor result set	Cursor query	Multiple rows of data are read at a time. All results are obtained through multiple reads.	The `OOM` error will not occur even when the data volume is large. Compared with a streaming query, a single cursor query occupies the database for a shorter time.	The execution is relatively slow. Compared with a streaming query, a cursor query consumes more server resources with a longer response time.

Note

When the text protocol is executed, full result sets are used in OceanBase Database no matter in Oracle mode or MySQL mode.

The following table lists the trigger conditions of the three result set categories in MySQL mode and Oracle mode.

Result set category	MySQL mode	Oracle mode
Cursor result set	* The URL connection string is set to `&useCursorFetch=true`. * The result set type is `TYPE_FORWARD_ONLY`. * The result set concurrency is `recur_read_only`. * The value of FetchSize is greater than 0.	The URL connection string is set to `&useServerPrepStmts=true` or `&useCursorFetch=true`. Note When `useCursorFetch` is set to `true`, `useServerPrepStmts` is automatically set to `true`.
Streaming result set	* The result set type is `TYPE_FORWARD_ONLY`. * The result set concurrency is `CONCUR_READ_ONLY`. * The value of FetchSize is the same as that of `Integer.MIN_VALUE`	None.
Full result set	Full result sets are used unless the trigger conditions for cursor result sets or streaming result sets are met.	The URL connection string is set to `&useServerPrepStmts=false`.