Hi,

Try to transform

the In clause with Exists clause

update article a set a.enabled=1
where exists (select 1 from ARTICLE_CONTENTTREE_LINK al
INNER JOIN SP_CT_VIDLIST_WITHROOT(104017) s ON
(al.TREEID = s.RTREEID) where al.articleid=a.relid)

regards

Alexandre
--- In firebird-support@yahoogroups.com, "nilsboedeker"
<nils.boedeker.support@...> wrote:

>
> Hi
>
> I have the following update sequenz:
>
> ------------------------------------------------
> UPDATE ARTICLE SET ENABLED=1
> WHERE RELID IN
> (
> SELECT
> ARTICLE_CONTENTTREE_LINK.ARTICLEID
> FROM ARTICLE_CONTENTTREE_LINK
> INNER JOIN SP_CT_VIDLIST_WITHROOT(104017) ON
> (ARTICLE_CONTENTTREE_LINK.TREEID = SP_CT_VIDLIST_WITHROOT.RTREEID)
> )
> ------------------------------------------------
>
> (SP_CT_VIDLIST_WITHROOT ist a Stored Procedure)
>
> The Query have the flowing plan sequenz:
>
> ------------------------------------------------
>
> Plan:
> PLAN JOIN ((SP_CT_VIDLIST NATURAL)
> PLAN (CONTENTTREE INDEX (RDB$PRIMARY2)), ARTICLE_CONTENTTREE_LINK
> INDEX (RDB$PRIMARY52))
> PLAN (ARTICLE NATURAL)
>
> Adapted plan:
> PLAN JOIN ((SP_CT_VIDLIST NATURAL)
> PLAN (CONTENTTREE INDEX (PK_CONTENTTREE)), ARTICLE_CONTENTTREE_LINK
> INDEXPK_ARTICLE_CONTENTTREE_LINK52))
> PLAN (ARTICLE NATURAL)
> 11 record(s) was(were) updated in ARTICLE
>
> ------------------------------------------------
>
> I have an unique Index on ARTICLE.RELID... but Firebird don't use this
> indes for this query... this means that the query needs a lot of time.
>
> What is neccecary to force firebird to use this index?
>
> Nils Bödeker
>