Re: ORDER BY question

I have asked the architecture group for some examples but so far I have not got anything from them yet. I then went on to do a bit more research myself. First of all, I found that the optimizer mode of our system is set to be FIRST_ROWS (we are on 10g). On one example that ORDER BY seems to slow down things, the query is trying to order by one column on which there is an index. This results in full scan of the index. Because the table is big and the values of the particular column are highly diversed, the scan can take a long time, especially when nothing meets the search conditions can be found. When I changed the query to use the ALL_ROWS mode, the query seems to have good performance on average.

Our application is developed in Java and uses JDBC to communicate with the Oracle DB. In most cases, nothing will be returned until we have traversed to the end of the result set. Therefore, I really don't quite understand why the architecture group wants to set the optimizer mode to FIRST_ROWS. Any thoughts on that?

On Dec 8, 9:00 am, DA Morgan <damor..._at_psoug.org> wrote:
> baoqi..._at_gmail.com wrote:
> > The architecture group of our company has told us to avoid using ORDER
> > BY in our queries. Their reasons are:
>
> > - In 10G, order by is incredibly inconsistent. They had an example with
> > only 2 rows of data that was extremely slow. Even if it looks like it
> > works alright, it's not guaranteed to, and we can't predict when
> > it'll fail. That instability is one of the main reasons against it.
>
> > - ORDER BY a primary key, especially when the primary key is not used
> > in the where clause, has the worst performance and is definitely to be
> > avoided.
>
> > Is this true?Complete and total rubbish.
>
> Any statement such as they made ... not backed up with a 10053 trace
> is just personal opinion.
>
> ORDER BY is expensive ... but "expensive" is a relative term. As others
> have said ... get the metrics.
>
> And if ORDER BY is ever the issue then look into implementing SORTED
> HASH CLUSTERS.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Received on Wed Dec 13 2006 - 22:32:43 CST