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?