> After various tweakings, we managed to narrow down the behaviour to be
> caused by the ORDER BY part of the query. Thus if we don't have an
> ORDER BY clause - the query runs considerably faster. (in the
> milliseconds) However, we DO have to have an ORDER BY in the query and
> it is not an option to have a seperate view for each one of the
> possible fields that we could possibly be ordering by in this
> dynamically generated query (since there are many possible
> combinations).
>
> We have tried setting the sort_area_size to a large value - but this
> did not result in any measurable performance improvement.
Hi,
the performance of a query with order by compared to the same query without
order by is highly dependent on the size of the result set. If it's large,
huge differences occur, because oracle has to retrieve the entire set and
re-sort it. Then it will provide the first data to the client.
Additionally, as oracle knows that order by is a set operation, the
execution plan tends to contain hash joins opposed to nested-loop-joins.
So, check the size of your result set and compare the execution plans.
Regards
Stephan
Received on Fri May 02 2003 - 03:58:30 CDT
