Hi,

> I have the following slow query
> SELECT * FROM PSearch WHERE PetName like 'JETHRO%' Order by
> PetName,OwnerLName
> firebird comes up with this plan for it
> PLAN SORT (JOIN (PSEARCH O NATURAL,PSEARCH P INDEX

(PETNAMEONLY,OWNERNUM4)))

> this plan has an execution time of 00:00:00.0651 with a prepare time of
> 00:00:00:0010 according to ibconsole,
> however if I specify the following plan
> PLAN JOIN (PSEARCH P INDEX (PETNAMEONLY),PSEARCH O INDEX (OWNERIDOWNERS))
> the plan has an execution time of 00:00:00.0010 and a prepare time of
> 00:00:00:0000.

<snip>

> PSearch is a view which is described as follows
> select

<snip>

> from Pets p join Owners o on o.OwnerID = p.OwnerID
>
> rebuilding indexes has no effect, and index selectivity is recalculated
> twice a day.
> is my only solution to keep specifying the plan whenever this query
> comes up? I would love to have some insight as to this query.

The problem here is that FB doesn't use STARTING WITH and IS NULL
conjunctions for determing join order. The only way is working around it,
but because you've a view it becomes more difficult and can effect other
queries.

Workaround is creating VIEW as :

SELECT
<fields>
FROM
Pets p
JOIN Owners o ON (o.OwnerID = p.OwnerID + 0)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
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 Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81