--- In firebird-support@yahoogroups.com, Harriv <harriv@...> wrote:

>
> Hi,
>
> I did small experiment storing hierarchical data, and now I'd like to
> optimize my query when retrieving tree.
>
> I'm using the Modified Preorder Tree Traversal described here:
> http://articles.sitepoint.com/article/hierarchical-data-database/2
>
> My query is:
>
> SELECT
> *
> FROM
> TEXT_DATA T
> WHERE
> T.POSITION_TAG IN (SELECT
> PSM.POSITION_TAG
> FROM
> POSITION_SCREEN_MAP PSM
> WHERE
> PSM.LFT BETWEEN (SELECT
> POSITION_SCREEN_MAP.LFT
> FROM
> POSITION_SCREEN_MAP
> WHERE
> POSITION_SCREEN_MAP.POSITION_TAG = 'AREA1') AND (SELECT
> POSITION_SCREEN_MAP.RGHT
> FROM
> POSITION_SCREEN_MAP
> WHERE
> POSITION_SCREEN_MAP.POSITION_TAG = 'AREA1')
> )
>
> The plan is:
>
> PLAN (POSITION_SCREEN_MAP INDEX (PK_POSITION_SCREEN_MAP))
> PLAN (POSITION_SCREEN_MAP INDEX (PK_POSITION_SCREEN_MAP))
> PLAN (PSM INDEX (PK_POSITION_SCREEN_MAP))
> PLAN (TEXT_DATA NATURAL)
>
> If I've understood correctly, IN operator isn't very optimizable, is
> there way to avoid using it?
>

hi Harriv,

you could be clearer about what you expecting retrieve?
reading that paper about Modified Preorder Tree Traversal I see that they are using only one table, but you are using 2 different tables (one of them in more than one place) but you are not using FK's... so your code is strange... can you explain it?
Also, what version of FB you are using?

regards,