db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel Tripp" <dtr...@mdisolutions.com>
Subject A fast row count with DatabaseMetaData.getIndexInfo() / tableIndexStatistic / CARDINALITY?
Date Fri, 02 May 2008 21:51:05 GMT
Hello all.  I am looking for a fast way to find out the number of rows in a

I am hoping for something faster than "select count(*) from X", which can
sometimes take a bit too long for me.  On one 2-million-row table of mine,
it takes 17 seconds on my machine.  Maybe I'm greedy, but I had an idea that
I could get the row count with DatabaseMetaData.getIndexInfo() instead (and
hopefully this would be faster than "select count(*) from X").  But
unfortunately the data returned by getIndexInfo() are not filling the bill.

The javadocs for java.sql.DatabaseMetaData.getIndexInfo() state:
"CARDINALITY int => When TYPE is tableIndexStatistic, then this is the
number of rows in the table...."


And that is the nugget that I am after.  But Derby's
DatabaseMetaData.getIndexInfo() seems never to return a row with TYPE ==
DatabaseMetaData.tableIndexStatistic.  The following program (which produced
the same results on Derby and shows what I mean:

import java.sql.*;
public class GetNumberOfRowsViaMetaData {
	static public void main(String args_[]) throws Exception {
		String url =
		Connection conn =
		conn.prepareStatement("create table THETABLE (I integer, S
		conn.prepareStatement("create index THEINDEX_ON_I on
THETABLE (I)").execute();
		conn.prepareStatement("create index THEINDEX_ON_S on
THETABLE (S)").execute();
		for(int i=0; i<42; ++i) { 
			PreparedStatement stmt =
conn.prepareStatement("insert into THETABLE values (?, ?)");
			stmt.setInt(1, i);
			stmt.setString(2, ""+i);
		ResultSet rset = conn.getMetaData().getIndexInfo(null, null,
"THETABLE", false, false);
		while(rset.next()) {
			String[] columns = new String[]{"TABLE_NAME",
			for(int i=0; i<columns.length; ++i) {
				System.out.println(columns[i]+" =
			if(rset.getShort("TYPE") ==
DatabaseMetaData.tableIndexStatistic) {
				System.out.println("--- Success ---");

When I run this program, I get the following output:

TYPE = '3'
TYPE = '3'

Note that a TYPE of 3 is DatabaseMetaData.tableIndexOther, which is not
useful to me for my row-counting purposes.  (I need tableIndexStatistic

Also note that "--- Success ---" is NOT printed.  When I run this same code
a Microsoft SQL Server database, though, it is printed.

I realize that the JDBC docs don't say that a row with TYPE equal to
tableIndexStatistic is /required/ to be returned by getIndexInfo().  But I
am wondering if anyone can help me out by commenting on any of this.  In

- why Derby's DatabaseMetaData.getIndexInfo() doesn't return a TYPE ==
tableIndexStatistic row 
- how Derby might be made to return one, if possible 
- any alternate way of achieving my goal of a fast row count 

Thanks in advance.

View raw message