Submitted by: Bill Oliver (verbguy)

Index metadata returned from SQLStatistics includes the columns PAGES and CARDINALITY. Information about PAGES isn't available through the system tables - there is a table RDB$PAGES, but testing shows it does not return information about the data pages, only the pointer pages.

Information about index CARDINALITY is available, and should be reported through the ODBC driver. For an index, the cardinality can be computed as (1/RDB$STATISTICS), from table RDB$INDICES. The query will probably look something like this:

CAST((CASE WHEN RDB$INDICES.rdb$statistics = 0 then 0 else 1 / RDB$INDICES.rdb$statistics END) AS BIGINT) as CARDINALITY.

Information about Table CARDINALITY is *not* available, this only affects Index CARDINALITY.

The cardinality/pages information is available to the optimizer, it's just not surfaced through the system tables. In version 3.0 of the server, we could add a monitoring table that would provide information on pages/cardinality on indexes and tables, too, but that is another day.

Here is some test data to assist you.

Thanks!

-- try with primary key
-- return 3 for cardinality
REcreate table statprimary (i integer NOT NULL);
alter table statprimary add constraint statprimary_pk primary key (i);
insert into statprimary values (1);
insert into statprimary values (2);
insert into statprimary values (3);
commit;

-- try with unique index
-- return 5 for cardinality
REcreate table statunique (i integer not null);
create unique index statunique_idx on statunique (i);
insert into statunique values (1);
insert into statunique values (2);
insert into statunique values (3);
insert into statunique values (4);
insert into statunique values (5);
commit;

-- try with non-unique index
-- return 4 for cardinality
REcreate table statindex (i integer not null);
create index statindex_idx on statindex (i);
insert into statindex values (1);
insert into statindex values (1);
insert into statindex values (2);
insert into statindex values (2);
insert into statindex values (3);
insert into statindex values (3);
insert into statindex values (4);
insert into statindex values (4);
commit;

-- just a constraint
RECREATE TABLE NOINDEX1 (I INTEGER NOT NULL);
INSERT INTO NOINDEX1 VALUES (1);
INSERT INTO NOINDEX1 VALUES (2);
INSERT INTO NOINDEX1 VALUES (3);
COMMIT;

-- nothing
RECREATE TABLE NOINDEX2 (I INTEGER);
INSERT INTO NOINDEX2 VALUES (1);
INSERT INTO NOINDEX2 VALUES (2);
INSERT INTO NOINDEX2 VALUES (3);
COMMIT;

set statistics index statprimary_pk;
set statistics index statunique_idx;
set statistics index statindex_idx;
commit; -- need this after set statistics

Commits: e9ec288 2f98d7e