Google
 

Friday, March 16, 2007

Approximate Record Count in Firebird

The following solution was originally posted to the Firebird Support Mail List by Ivan Prenosil.This solution only returns an approximate record count*, and will include records that have been deleted (and not yet garbage collected) as well as records that have been inserted but not yet committed.

Code:
/* first update the statistics */
UPDATE RDB$INDICES SET RDB$STATISTICS = -1;
COMMIT;

/* Display table names and record counts */
SELECT RDB$RELATIONS.RDB$RELATION_NAME,CASE WHEN RDB$INDICES.RDB$STATISTICS = 0 THEN 0 ELSE CAST(1 / RDB$INDICES.RDB$STATISTICS AS INTEGER) END FROM RDB$RELATIONS LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATIONS.RDB$RELATION_NAME = RDB$RELATION_CONSTRAINTS.RDB$RELATION_NAME AND RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'LEFT JOIN RDB$INDICES ONRDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDICES.RDB$INDEX_NAME WHERE RDB$VIEW_BLR IS NULL ANDRDB$RELATION_ID >= 128 ORDER BY 1;


* This will only work on tables that have a primary key.

5 comments:

David Sanginés said...

Really usefull, but...
I have a problem with this approach.

I had a table with 1 million records, now I delete 600,000 records, but the statistics of the index didn't change, so the count of the records of the table continue with 1 million, any suggestion?

I already try to drop the PK and recreate it but it didn't work.

I guess the problem is the garbage, so how can i tell fb that collect garbage?

I'm using Firebird V2.0 in Suse Linux 10.1

thanks

David Sanginés

Anonymous said...

hello

Anonymous said...

kraków kwiaciarnia
apartamenty kraków
pozycjonowanie
kwiaciarnia kraków
los angeles seo company
new technology blog
coupons saver printable
seokatalog
rękawice robocze
ręczniki
części elektroniczne
pościel satynowa
wczasy w polsce
posadzki epoksydowe
meble kraków
lampy wiszące
lampy sufitowe
lampy do salonu
city breaks kraków
lampy sklep internetowy
logo
honda części
narkomani
narkomania
terapie
terapie
help alko
lekomania
lekarz
scotts fertilizer
blogik
drinking problem
perkins restaurant coupon
mud tires On sale
countertop
wiszace lampy
lampy design
buvar shop
lampy salonowe
lampy wiszace
lampy luxusowe
lampy dekora
lampy ladne
lampy nowe
kredyt
kredyt
kredyt
kredyt
kredyt
opieka nad grobami kraków

sexual intercourse said...

I suppose one and all should browse on it.

Adrian Moczyński said...

Jestem pod wrażeniem. Bardzo ciekawie napisany artykuł.