How to take data in portions from Oracle using Mybatis?

Question

In my application I am making a query to oracle and getting data this way

<select id="getAll" resultType="com.mappers.MyOracleMapper">
    SELECT * FROM "OracleTable"
</select>

I get all the data, the problem is that there is a lot of data and it will take too much time to process all the data at once, since the response from the database will come in 3-4 minutes, this is not convenient. How to make it so that I receive lines in portions without using the id field (since it does not exist, I do not know why). That is, take the first portion of lines, for example, the first 50, process them and take the next portion. It would be desirable to place a variable in properties that will be responsible for the number of lines in portions. I can't do this in mybatis. This is new to me. Thanks in advance. there is such a field and it is unique

 OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY

don't work, because the version is earlier than 12c

There may be several options. You should be able to use [`ROWNUM`](https://stackoverflow.com/a/470571/1261766) in Oracle < 12. Or, you can try [`Cursor`](https://mybatis.org/mybatis-3/java-api.html#SqlSession) to fetch rows lazily. — ave, Feb 14 '21 at 19:31

score 0 · Answer 1 · answered Feb 15 '21 at 14:57

If you want to read millions of rows that's going to take time. It's normal to expect a few minutes to read and receive all the data over the wire.

Now, you have two options:

Use a Cursor

In MyBatis you can read the result of the query using the buffering a cursor gives you. The cursor reads a few hundred rows at a time and your app reads them one by one. Your app doesn't notice that behind the scenes there is buffering. Pretty good. For example, you can do:

Cursor<Client> clients = this.sqlSession.selectCursor("getAll");
for (Client c : clients) {
  // process one client
}

Consider that cursors remain open until the end of the transaction. If you close the transaction (or exit the method marked as @Transactional) the cursor won't be usable anymore.

Use Manual Pagination

This solution can work well for the first pages of the result set, but it becomes increasingly inefficient and slooooooow the more you advance in the result set. Use it only as a last resort.

The only case where this strategy can be efficient is when you have the chance of implementing "key set pagination". I assume it's not the case here.

You can modify your query to perform explicit pagination. For example, you can do:

<select id="getPage" resultType="com.mappers.MyOracleMapper">
  select * from (
    SELECT rownum rnum, x.* 
    FROM OracleTable
    WHERE rownum &lt;= #{endingRow}
    ORDER BY id
  ) x
  where rnum >= #{startingRow}
</select>

You'll need to provide the extra parameters startingRow and endingRow.

NOTE: It's imperative you include an ORDER BY clause. Otherwise the pagination logic is meaningless. Choose any ordering you want, preferrably something that is backed up by an existing index.

How to take data in portions from Oracle using Mybatis?

1 Answers1