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. |