At 10:00 AM 12-12-02 +0000, you wrote:

>Thank you for all who answered my questions. I have found out that
>the problems is relateded to sorting a columns which are not in the
>table of SELECT FROM clause. i.e. I am try to sort A.NAME which is
>not in table HERBAL that I am selecting from.
>
>I solve the query by arranging the order of table in the select from:
>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 (some values);
>
>Now sorting on A.NAME, A.STROKE and A.S_CODE work correctly.
>However this query proforms less effective than the previous one.
>Any ideas to optimise this query?

Although query optimisation is off-topic here, I will make an exception
because today is a special day. :-)

Your problem is that WHERE clause. IN(..) should be avoided here. Apart
from the fact that it prevents the use of indexes for that weird right
join, the IN(set) will blow up at a relatively small size (around 1K
members, if memory serves correctly). If possible, use a range or other
limiting expression instead, e.g.

WHERE HC.HC_REFNO between SomeNumber and SomeOtherNumber

or

WHERE HC.HC_REFNO > SomeNumber

or

WHERE HC.HC_REFNO STARTING WITH SomeString

None of these predicates will prevent the optimiser from using the indexes
on REFNO in the two tables.

I say the right join is weird, because your query returns no columns from
HC, so you are going to get rows of nulls for every case where a REFNO is
in HC but not in A. I can't imagine how anyone would make use of a dataset
like that.

Helen