[Firebird-devel] ODBC SQLStatistics and PAGES value

[Bill O. <bil...@sa...>]

Hello all!

A user wants to know if he can retrieve statistics information about 
Firebird tables, specifically the Table information exposed through ODBC's 
SQLStatistics call as CARDINALITY and PAGES. He wants to get at this 
information through the Firebird system tables.

ODBC definitions...

CARDINALITY - Cardinality of table or index; number of rows in table if TYPE 
is SQL_TABLE_STAT; number of unique values in the index if TYPE is not 
SQL_TABLE_STAT; NULL is returned if the value is not available from the data 
source.

PAGES - Number of pages used to store the index or table; number of pages 
for the table if TYPE is SQL_TABLE_STAT; number of pages for the index if 
TYPE is not SQL_TABLE_STAT; NULL is returned if the value is not available 
from the data source or if not applicable to the data source.

I know that you can retrieve cardinality for an INDEX, using RDB$STATISTICS 
in RDB$INDICES, computing it like
CAST((CASE WHEN idx.rdb$statistics = 0 then 0 else 1 / idx.rdb$statistics 
END) AS BIGINT) as CARDINALITY. This is provided your statistics are up to 
date. I looked at the ODBCJDBC driver, and it doesn't seem to return this 
information, though.

Proposal - what do you all think about adding a new column to RDB$RELATIONS, 
call it RDB$PAGES, which would return the number of pages associated with 
this TABLE? It seems easy to do, and would be nice feature for ODBC driver 
and users who want this kind of info.

I could see adding a column RDB$CARDINALITY, which would return # of rows in 
the table. This could be computed as the # of database pages divided by the 
record length of one row. The value would be *very* approximate, so perhaps 
this idea is not worth doing, but I mention it in case someone has better 
idea.

I can enter a tracker for far future if you all think this is desirable.

Apologies if this is already there somewhere and I missed it...

-bill