We are having a performance issue with "order by" and "contains". If I remove the "order by" from the query, the query returns less than 1 second, but the query takes 10 seconds with "order by". The number of rows returned is only around 100.
In 11g you can add an "order by" clause to the index, but not in 10g.
When you say "The number of rows returned is only around 100", is that the total number of rows returned by the query, or does that mean you're only fetching the first 100 rows? I really can't see why it should take 9 seconds to sort 100 rows.