Searched high and low for getting the number of rows in a table in Oracle with millions of rows. And the best solution is what i found below.
Used Primary key to count the rows, because it is indexed and so the transaction is fast.
If the primary key is a composite key I used one column from the composite key.
I have a class DBConnection with all the methods for connecting and closing the connection.
public static int getRows(String table) {
String METHOD_STRING = "getRows-- ";
Statement _statement_ = null;
ResultSet _result_Set = null;
Connection _connection_ = null;
int rowCount = 0;
String getRowsQuery = "";
String space = " ";
String SELECT_PARALLEL = "SELECT /* PARALLEL */";
try {
_connection_ = DBConnection.getConnection();
if (_connection_ != null) {
_statement_ = _connection_.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
} else {
logger.info(METHOD_STRING + "Connection object in " + METHOD_STRING + " is null");
}
if (!table.isEmpty() && !table.equals("")) {
switch (table.toLowerCase()) {
case "product":
getRowsQuery = SELECT_PARALLEL + space + "COUNT(PRIMARY_KEY) FROM" + space + table;
break;
case "review":
getRowsQuery = SELECT_PARALLEL + space + "COUNT(PRIMARY_KEY) FROM" + space + table;
break;
case "customer":
getRowsQuery = SELECT_PARALLEL + space + "COUNT(PRIMARY_KEY) FROM" + space + table;
break;
}
logger.info("---------> " + getRowsQuery);
if (_statement_ != null) {
_result_Set = _statement_.executeQuery(getRowsQuery);
} else {
logger.info(METHOD_STRING + "Statement object in " + METHOD_STRING + " is null");
}
if (_result_Set != null) {
_result_Set.next();
rowCount = _result_Set.getInt(1);
logger.info("The number of rows in the table " + table + " is----> " + rowCount);
}
}
} catch (SQLException sqle) {
// TODO Auto-generated catch block
logger.error(METHOD_STRING + sqle.toString());
} catch (Exception ex) {
logger.error(METHOD_STRING + ex.toString());
} finally {
DBConnection.close(_connection_, _statement_, _result_Set);
}
return rowCount;
}
