Well I am happy that today is Firebird foundation day and my
question really get answered. Anyway I do know that my query is
some kind weired, but it works.

Let me explain a bit more...
HERBAL_CAT is a table for storing herbal's category with parent
relationship.
HERBAL_WITH_CAT is a table that store which herbal is under which
category.
I want to select all herbal that is under a specific category, which
may also contains many child category as well. The program make up
a string when the user select a category. The string look like this:
'2, 24, 25, 26, 33, 34, 36, 37'. The first one is the category
selected, the rest are its child category.
Now I put this string into the where clause and the query becomes
SELECT H.REFNO
, A.NAME
, A.STROKE
, A.S_CODE
, H.DOSAGE
, H.CHARACTERISTIC
, H.SYSTEM
, H.FUNC
, H.USES
, H.REMARK
FROM ALL_HERBAL A
RIGHT JOIN HERBAL_WITH_CAT HC
ON HC.H_REFNO = A.REFNO
JOIN HERBAL H
ON H.REFNO = A.REFNO
WHERE HC.HC_REFNO in (2, 24, 25, 26, 33, 34, 36, 37);
// range cannot apply here

The plan is:
PLAN SORT(MERGE(SORT(H NATURAL),SORT (JOIN HC INDEX
(PK_HERBAL_WITH_CAT,
RDB$PRIMARY6,RDB$PRIMARY6,RDB$PRIMARY6,RDB$PRIMARY6,RDB$PRIMARY6,RDB$
PRIMARY6,RDB$PRIMARY6,RDB$PRIMARY6,RDB$PRIMARY6), A INDEX
(IDX_ALL_HERBAL)))))
// it seems it does use index after all

The execution is quite fast. If I don't use the RIGHT JOIN then the
query is slow like:
SELECT
...
FROM ALL_HERBAL A, HERBAL H
WHERE A.REFNO=H.REFNO
AND A.REFNO IN (SELECT HC.H_REFNO FROM HERBAL_WITH_CAT HC
WHERE HC.HC_REFNO IN (2, 24, 25, 26, 33, 34, 36, 37))

Any Idea?