Re: ORDER BY question

On Dec 14, 5:32 am, baoqi..._at_gmail.com wrote:
> 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- Hide quoted text -- Show quoted text -

First_rows is a common trick to make CBO behave like RBO, used by people who are conservative, and do not want to learn how CBO works.

-- 
Sybrand Bakker
Senior Oracle DBA