Menu
▾
▴
firebird-devel
|
From: Bill O. <bil...@sa...> - 2009-03-05 16:26:38
|
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 |
|
From: Alexander P. <ale...@re...> - 2009-03-05 17:48:30
|
Bill Oliver wrote: > 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. > > 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. > > Hi Bill, For that matter, I can add this logic into the ODBC driver in RC2 version maybe. Regards, Alexander Potapchenko |
|
From: Adriano d. S. F. <adr...@gm...> - 2009-03-05 17:59:11
|
Bill Oliver escreveu: > 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? select count(*) from rdb$pages where rdb$relation_id = ?; Adriano |
|
From: Bill O. <bil...@sa...> - 2009-03-05 18:42:16
|
> select count(*) from rdb$pages where rdb$relation_id = ?; Yea... That would be one way to do it wouldn't it? :-) This would also be good to add to the ODBC driver. Thanks! -b |
|
From: Alexander P. <ale...@re...> - 2009-03-05 19:05:16
|
Bill Oliver wrote: >> select count(*) from rdb$pages where rdb$relation_id = ?; >> > > Yea... That would be one way to do it wouldn't it? :-) > > This would also be good to add to the ODBC driver. > > Thanks! > > Please add this request to the tracker and I will schedule it for RC2. Regards, Alexander |
|
From: Vlad K. <hv...@us...> - 2009-03-05 19:08:28
|
> Bill Oliver escreveu:
>> 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?
> select count(*) from rdb$pages where rdb$relation_id = ?;
It would count pointer pages, not data pages ;)
Regards,
Vlad
|
|
From: Bill O. <bil...@sa...> - 2009-03-05 20:47:55
|
>> select count(*) from rdb$pages where rdb$relation_id = ?;
>
> It would count pointer pages, not data pages ;)
>
Nuts!!!
In Helen's book, it says on page 969 that RDB$PAGE_TYPE identifies the type
of data stored on the page (table data, index, etc.). But, Vlad is right,
the data pages aren't printed.
Look at this output for table #132. There are 62 data pages, but we only get
2 rows from the query against the system table. The data pages, where page
type = 5, aren't printed.
I'm sure there is a reason why this was done... Anyone know?
C:\SASv9\fbembed>gstat c:\fbdata\fbtest.tdb -u sysdba -p masterkey
Database "c:\fbdata\fbtest.tdb"
Database header page information:
Flags 0
Checksum 12345
Generation 117
Page size 4096
ODS version 11.0
Oldest transaction 51
Oldest active 113
Oldest snapshot 113
Next transaction 114
Bumped transaction 1
Sequence number 0
Next attachment ID 2
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Mar 5, 2009 10:16:17
Attributes
Variable header data:
*END*
Database file sequence:
File c:\fbdata\fbtest.tdb is the only file
Analyzing database pages ...
PTEST (131)
Primary pointer page: 231, Index root page: 232
Data pages: 1, data page slots: 1, average fill: 2%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
TESTY (130)
Primary pointer page: 227, Index root page: 228
Data pages: 1, data page slots: 1, average fill: 2%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
TKTSTYPE (132)
Primary pointer page: 155, Index root page: 156
Data pages: 62, data page slots: 62, average fill: 51%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 61
60 - 79% = 0
80 - 99% = 0
C:\SASv9\fbembed>isql c:\fbdata\fbtest.tdb -u sysdba -p masterkey
Database: c:\fbdata\fbtest.tdb, User: sysdba
SQL> select * from rdb$pages where rdb$pages.rdb$relation_id = 132;
RDB$PAGE_NUMBER RDB$RELATION_ID RDB$PAGE_SEQUENCE RDB$PAGE_TYPE
=============== =============== ================= =============
155 132 0 4
156 132 0 6
SQL>
|
|
From: Dmitry Y. <fir...@ya...> - 2009-03-05 20:29:32
|
Bill et al, > CARDINALITY > PAGES Both values are available to the optimizer, they're just not surfaced. > Proposal - what do you all think about adding a new column to RDB$RELATIONS This sounds as a bad idea to me. I don't like any runtime "magic" fields in persistent system tables. Even if we speak about a real computed field, then it's going to be computed via some BLR anyway, so we could be publicly expose it as a built-in function. There's also another option. I expect v3.0 to have all its database statistics (i.e. GSTAT output) stored in the database itself and refreshed periodically. This is a separate subject which will be discussed later. But if this would be implemented, then we could surface all that stored statistics via a bunch of new monitoring tables. Dmitry |
|
From: Bill O. <bil...@sa...> - 2009-03-05 21:05:57
|
> There's also another option. I expect v3.0 to have all its database > statistics (i.e. GSTAT output) stored in the database itself and > refreshed periodically. This is a separate subject which will be > discussed later. But if this would be implemented, then we could surface > all that stored statistics via a bunch of new monitoring tables. Very good. I will defer this to 3.0 then. Thanks! -b |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X