I think you solved it, now FB2 is coming up with good plans. Now the
question is, why do all my primary key indices have no statistics
computed for them?

SELECT
i.RDB$RELATION_NAME,
i.RDB$INDEX_NAME,
i.RDB$STATISTICS,
ins.RDB$FIELD_POSITION,
ins.RDB$FIELD_NAME,
ins.RDB$STATISTICS
FROM
RDB$INDICES i
JOIN RDB$INDEX_SEGMENTS ins ON (ins.RDB$INDEX_NAME = i.RDB$INDEX_NAME)
WHERE
/* grab only primary keys */
i.RDB$INDEX_NAME LIKE 'PK_%'
ORDER BY
i.RDB$RELATION_NAME, i.RDB$INDEX_NAME, ins.RDB$FIELD_POSITION

Returns 0 in both statistic columns.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Arno Brinkman
Sent: Thursday, November 30, 2006 6:39 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] FB 2.0 optimizer dogging us

Hi Rick,

>> Did you run backup/restore before using in under FB2?
> No backup/restore. I ran the DDL scripts to create the domains and
> tables, copied over the data, then the scripts to create the
> constraints, indexes, views, and stored procedures.

Ok, i understand on a new created database under FB2? Thus which has
ODS11.

> Data may be slightly different as the snapshots are a few days apart.
>
> FB 2.0

Damn outlook wrapped those lines, but..

> ACT_CHC_PHARM_SUBACCT PK_ACT_CHC_PHARM_SUBACCT 0 0
> CHC 0
> ACT_CHC_PHARM_SUBACCT PK_ACT_CHC_PHARM_SUBACCT 0 1
> PHARMACY 0

Run :
SET STATISTICS INDEX PK_ACT_CHC_PHARM_SUBACCT;

> PBM_CLAIM PK_PBMCLAIM 0 0
> ID 0

Run :
SET STATISTICS INDEX PK_PBMCLAIM;

> RXS_CLM_CALC_FEES PK_RXSCLMCALCFEES 0 0
> CLAIM_ID 0

Run :
SET STATISTICS INDEX PK_RXSCLMCALCFEES;

And try your queries again.

I think it would be better to run once SET STATISTICS for every index.
You could write an simple script with the EXECUTE BLOCK statement that
does that for you.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features
:
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info

------------------------------------------------------------------------
--------
Mijn Postvak In wordt beschermd door SPAMfighter
954 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links




Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.