FWIW, the query takes seven seconds. The following union takes only 0.8
seconds.
SELECT FIRST 1 1 FROM INVC_GRANTEE_INVC g WHERE g.ID = ?
union
SELECT FIRST 1 1 FROM INVC_RXS_INVC r WHERE r.ID = ?
union
SELECT FIRST 1 1 FROM INVC_PHARM_BILLGRP_INVC b WHERE b.ID = ?

For some reason the previous statement didn't work in the CHECK, FB
complained that UNION was unknown.
I changed it to three separate select statements.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Thursday, June 02, 2005 3:47 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Puzzling plan (or lack of)

I have four tables that share a common generator for their ID.
I also have a check constraint to make sure that an ID is never set that
is already in use.
The puzzling thing is that the plan makes no use of indexes (ID is the
PK for each table).

ALTER TABLE INVC_WHLSLR_INVC ADD CONSTRAINT C_WHSLEINV_ID CHECK (
/* ID is NULL, so generator will assign it */ NEW.ID IS NULL OR (
/* make sure ID isn't in use by another table */ SELECT FIRST 1
1
FROM
INVC_GRANTEE_INVC g,
INVC_RXS_INVC r,
INVC_PHARM_BILLGRP_INVC b
WHERE
g.ID = NEW.ID OR
r.ID = NEW.ID OR
b.ID = NEW.ID
) IS NULL );

PLAN JOIN (R NATURAL,G NATURAL,B NATURAL)


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

Visit http://firebird.sourceforge.net 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