G'day all...

Here's a curly one for you - I just hope I can explain it properly,
so I apologise in advance for the lengthy message. I have a schema in which
I have a self-referencing table (TYPES) and some stored procedures which use
it to manage a hierarchical tree structure of "types" and "subtypes"
(relevant schema details below). The stored procedures are potentially
fairly costly because they are recursive, but this is the only way I can
think of to be able to use "subtypes" meaningfully in join operations.
Everything works fine, but I am trying to optimise things so that I can make
better use of this setup. So far, I have formulated all my queries so that
result set of these stored procedures is invariant in the context of the
overall query, and I have been forcing Firebird to execute them once only by
placing them at the head of the FROM clause so that they are not executed
multiple times for a join operation.

Now: I believe I should be able to use the PLAN clause to optimise
the execution of these nasty stored procedures to allow me to have more
flexibility in where I place them in a query, and herein lies my problem.
The queries work quite happily but the default plan shown for each of them
doesn't seem to make any sense, so I have nothing on which to base a custom
plan clause. This is the first time I've messed with a PLAN clause, and
Helen Borrie's otherwise excellent treatment of the subject in "The Firebird
Book" doesn't seem to cover my (admittedly weird) situation.

For example, I am trying to slicken up the following query. The
problem I have is that the line "left join getallsubtypes(1, 2) mo"... bogs
the query down because it seems to be re-executed unneccessarily for the
downstream join. Note that pretty much all of my tables (besides the TYPES
table) have single, numeric primary keys with enforced referential
integrity. Note that I have also included a highly-simplified abbreviated
version of this query further below which better highlights the PLAN
problem, but I wanted to show why I need to mess with the PLAN clause in the
first place:

/* Advertising Source Report Query */
select re.regionname as "Region",
cast(extract(year from m.firstcontactdate) || '-' || extract(month from
m.firstcontactdate) || '-01' as date) as "Call Month",
ast.typename as "General Advertising Source",
adt.typename as "Detailed Advertising Source",
ct.typename as "Call Type",
count(m.marketingid) as "Contact Count",
count(mo.submetatypeid) as "Mail-out Count",
count(q.quoteid) as "Quote Count",
count(j.jobid) as "Job Count",
sum(q.quotevalue) as "Quoted Value",
sum(j.jobprice) as "Work Value"

from getroottypes(3, NULL) rast /* Root advertising source types */
inner join marketing m on m.advertisingsourcemetatypeid = 3 and
m.advertisingsourcetypeid = rast.subtypeid
left join person p on p.marketingid = m.marketingid
left join locality l on m.localityid = l.localityid
left join postcode pc on l.postcodeid = pc.postcodeid
left join territory te on pc.territoryid = te.territoryid
left join region re on te.regionid = re.regionid
left join interaction fi on fi.marketingid = m.marketingid
left join interaction i on i.personid = p.personid
left join getallsubtypes(1, 2) mo on mo.submetatypeid = i.actionmetatypeid
and mo.subtypeid = i.actiontypeid /* All mail-out types - this is what bogs
things down /*
left join quote q on q.actionid = i.actionid and (bin_and(q.quotestatus, 1)
= 1)
left join job j on j.actionid = i.actionid and (bin_and(j.jobstatus, 1) = 1)
and (j.jobprice > 0)
left join types adt on adt.metatypeid = m.advertisingsourcemetatypeid and
adt.typeid = m.advertisingsourcetypeid
left join types ct on ct.metatypeid = fi.actionmetatypeid and ct.typeid =
fi.actiontypeid
left join types ast on ast.metatypeid = 3 and ast.typeid = rast.roottypeid

where adt.typename is not null
and te.RegionID = 31
and cast(m.firstcontactdate as date) >= '2007-01-01'
and cast(m.firstcontactdate as date) <= '2007-12-13'

group by 1, 2, 3, 4, 5
order by 1, 2, 3, 4, 5

The preceding query uses the following default PLAN, which seems to be
garbled, possibly due to the use of the stored procedures:

PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN
(JOIN (JOIN (JOIN (JOIN (GETALLSUBTYPES NATURAL, TYPES INDEX (FK_TYPE_1)), M
INDEX (FK_MARKETING_3)), P INDEX (FK_PERSON_3)), L INDEX (PK_LOCALITY)), PC
INDEX (PK_POSTCODE)), TE INDEX (PK_TERRITORY)), RE INDEX (PK_REGION)), FI
INDEX (UNQ1_INTERACTION)), I INDEX (FK_INTERACTION_2)), GETSUBTYPES
NATURAL), TYPES INDEX (FK_TYPE_1)), Q INDEX (FK_QUOTE_2)), J INDEX
(UNQ1_JOB)), ADT INDEX (PK_TYPE))(CT INDEX (PK_TYPE))(AST INDEX (PK_TYPE))))




A simplified case derived from the mother-of-a-query above would be as
follows:

/* Test Query using getroottypes stored procedure */
select *
from getroottypes(3, NULL) rast
inner join marketing m on m.advertisingsourcemetatypeid = 3 and
m.advertisingsourcetypeid = rast.subtypeid


This is the plan the optimiser apparently came up with:
PLAN JOIN (GETALLSUBTYPES NATURAL, TYPES INDEX (FK_TYPE_1))(M INDEX
(FK_MARKETING_3))

... and this is the plan I tried:
PLAN JOIN (rast NATURAL, M INDEX (FK_MARKETING_3))

which gives the following result:
Invalid token.
invalid request BLR at offset 152.
BLR syntax error: expected TABLE at offset 152, encountered 125.








Some relevant sections of the schema are as follows:

/* Generic type table representing all hierarchical types. */
CREATE TABLE TYPES (
METATYPEID SMALLINT NOT NULL,
TYPEID SMALLINT NOT NULL,
TYPENAME VARCHAR(64) NOT NULL COLLATE EN_US,
PARENTMETATYPEID SMALLINT,
PARENTTYPEID SMALLINT,
TYPESTATUS INTEGER NOT NULL,
TYPECODE VARCHAR(8) COLLATE EN_US,
TYPECOMMENT VARCHAR(254) COLLATE EN_US
);
ALTER TABLE TYPES ADD CONSTRAINT UNQ1_TYPE UNIQUE (METATYPEID, TYPENAME);
ALTER TABLE TYPES ADD CONSTRAINT PK_TYPE PRIMARY KEY (METATYPEID, TYPEID);
ALTER TABLE TYPES ADD CONSTRAINT FK_TYPES_1 FOREIGN KEY (METATYPEID)
REFERENCES METATYPE (METATYPEID) ON UPDATE CASCADE
USING INDEX FK_TYPE_1;
ALTER TABLE TYPES ADD CONSTRAINT FK_TYPES_2 FOREIGN KEY (PARENTMETATYPEID,
PARENTTYPEID) REFERENCES TYPES (METATYPEID, TYPEID) ON DELETE SET NULL ON
UPDATE CASCADE
USING INDEX FK_TYPES_1;


/* This stored procedure returns all nodes under the node specified using
the input parameters, NOT including the top node itself */
SET TERM ^ ;
CREATE PROCEDURE GETSUBTYPES (
metatypeid smallint,
typeid smallint)
returns (
submetatypeid smallint,
subtypeid smallint)
as
BEGIN
FOR
SELECT METATYPEID, TYPEID
FROM TYPES
WHERE (METATYPEID = :METATYPEID)
AND (((PARENTMETATYPEID = :METATYPEID) AND (PARENTTYPEID = :TYPEID))
OR ((PARENTTYPEID IS NULL) and (:TYPEID IS NULL)))
INTO :SUBMETATYPEID, :SUBTYPEID
DO
BEGIN
SUSPEND;
FOR
SELECT SUBMETATYPEID, SUBTYPEID FROM GETSUBTYPES(:SUBMETATYPEID,
:SUBTYPEID)
INTO :SUBMETATYPEID, :SUBTYPEID
DO
SUSPEND;
END
END^
SET TERM ; ^



/* This stored procedure returns all nodes under the node specified using
the input parameters, including the top node itself */
SET TERM ^ ;
CREATE PROCEDURE GETALLSUBTYPES (
metatypeid integer,
typeid integer)
returns (
submetatypeid integer,
subtypeid integer)
as
BEGIN
FOR
SELECT METATYPEID, TYPEID
FROM TYPES
WHERE (METATYPEID = :METATYPEID)
AND ((TYPEID = :TYPEID)
OR ((PARENTTYPEID IS NULL) AND (:TYPEID IS NULL)))
INTO :SUBMETATYPEID, :SUBTYPEID
DO
BEGIN
SUSPEND;
FOR
SELECT SUBMETATYPEID, SUBTYPEID FROM GETSUBTYPES(:SUBMETATYPEID,
:SUBTYPEID)
INTO :SUBMETATYPEID, :SUBTYPEID
DO
SUSPEND;
END
END^
SET TERM ; ^



/* This stored procedure returns the type ID of the topmost ancestor node
for all the nodes under the one specified using the input parameters */
SET TERM ^ ;
CREATE PROCEDURE GETROOTTYPES (
metatypeid smallint,
typeid smallint)
returns (
roottypeid smallint,
subtypeid smallint)
as
BEGIN
FOR
SELECT TYPEID
FROM TYPES
WHERE (METATYPEID = :METATYPEID)
AND ((TYPEID = :TYPEID)
OR ((PARENTTYPEID IS NULL) AND (:TYPEID IS NULL)))
INTO :ROOTTYPEID
DO
BEGIN

FOR
SELECT :ROOTTYPEID, SUBTYPEID FROM GETALLSUBTYPES(:METATYPEID,
:ROOTTYPEID)
INTO :ROOTTYPEID, :SUBTYPEID
DO
SUSPEND;
END
END^
SET TERM ; ^


/* This is one of the tables which references the TYPES table */
CREATE TABLE MARKETING (
MARKETINGID INTEGER NOT NULL,
ADVERTISINGSOURCEMETATYPEID SMALLINT NOT NULL,
ADVERTISINGSOURCETYPEID SMALLINT NOT NULL,
AGERANGEMETATYPEID SMALLINT NOT NULL,
AGERANGETYPEID SMALLINT NOT NULL,
GENDERCODE CHAR(1) NOT NULL COLLATE EN_US,
LOCALITYID INTEGER NOT NULL,
FIRSTCONTACTDATE TIMESTAMP NOT NULL
);
ALTER TABLE MARKETING ADD CONSTRAINT PK_MARKETING PRIMARY KEY (MARKETINGID);
ALTER TABLE MARKETING ADD CONSTRAINT FK_MARKETING_2 FOREIGN KEY (GENDERCODE)
REFERENCES GENDER (GENDERCODE) ON UPDATE CASCADE;
ALTER TABLE MARKETING ADD CONSTRAINT FK_MARKETING_3 FOREIGN KEY
(ADVERTISINGSOURCEMETATYPEID, ADVERTISINGSOURCETYPEID) REFERENCES TYPES
(METATYPEID, TYPEID) ON UPDATE CASCADE;
ALTER TABLE MARKETING ADD CONSTRAINT FK_MARKETING_4 FOREIGN KEY
(AGERANGEMETATYPEID, AGERANGETYPEID) REFERENCES TYPES (METATYPEID, TYPEID)
ON UPDATE CASCADE;
ALTER TABLE MARKETING ADD CONSTRAINT FK_MARKETING_5 FOREIGN KEY (LOCALITYID)
REFERENCES LOCALITY (LOCALITYID) ON UPDATE CASCADE;



I just hope somebody here (a) can be bothered looking all the way
through this, (b) has some idea of what I'm trying to do and (c) has some
idea of how to solve my problem. Basically, all I want to do is make sure
that my nasty stored procedures are not executed unneccessarily, thereby
bogging my queries down.

Thanks in anticipation,

Alex.