如何从大表中读取所有行? [英] How to read all rows from huge table?

问题描述

我在处理数据库 (PostgreSQL) 中的所有行时遇到问题.我收到一个错误:org.postgresql.util.PSQLException: Ran out of memory retrieving query results. 我认为我需要以小块读取所有行，但它不起作用 - 它读取只有 100 行(下面的代码).怎么做?

I have a problem with processing all rows from database (PostgreSQL). I get an error: org.postgresql.util.PSQLException: Ran out of memory retrieving query results. I think that I need to read all rows in small pieces, but it doesn't work - it reads only 100 rows (code below). How to do that?

    int i = 0;      
    Statement s = connection.createStatement();
    s.setMaxRows(100); // bacause of: org.postgresql.util.PSQLException: Ran out of memory retrieving query results.
    ResultSet rs = s.executeQuery("select * from " + tabName);      
    for (;;) {
        while (rs.next()) {
            i++;
            // do something...
        }
        if ((s.getMoreResults() == false) && (s.getUpdateCount() == -1)) {
            break;
        }           
    }

推荐答案

使用 PostgreSQL 中的 CURSOR 或 让 JDBC 驱动程序处理这个给你.

LIMIT 和 OFFSET 在处理大型数据集时会变慢.

LIMIT and OFFSET will get slow when handling large datasets.

这篇关于如何从大表中读取所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！