Re: Order By Bogs down the Statement

From: Mark Powell <Mark.Powell_at_eds.com>
Date: 6 Nov 1998 18:20:33 GMT
Message-ID: <01be09b2$70136320$a12c6394@J00679271.ddc.eds.com>

As a note to a reply I posted on why a query would slow down when an order by clause was added to it the following was posted. Note - reply referred to use of init.ora parameters for sort area and temp tablespace existence and parameters.

oliver.willandsen_at_sg.cec.be wrote in article 19981105202414.19650.00000018_at_ng134.aol.com>, l
> >

>>Rich and Mark,

>
> one other aspect to consider is that the order by clause may cause your
query
> to not use indexes anymore.
>
> order by clauses must meet to rigid requirements if indexes should still
> be used :
>
> 1) all of the columns that make up the order by clause must be contained
> within a single index in the same order
>
> 2) all of the columns that make up the order by clause must be defined as
NOT
> NULL in the table. NULL values are not contained within an index
>
> Regards
>
> --
> Oliver Willandsen
> European Commission
> http://europa.eu.int
>
>

Oliver, I could be wrong, but I do not believe that the two rules you posted are true in that adding an order by clause to a query will not necessarily prevent Oracle from using an index to solve the query. I have seen numerous explain plans where Oracle uses an index to retrieve rows and then performs a sort on columns that are not declared with a not null constraint. The columns in the index being used and the columns in the order by clause do not even have to match so how could using a nullible column in an order by clause cause Oracle to stop using the index.

The rules you posted seem to be necessary for the optimizer to eliminate the sort step by substituting an index ordered retrieval plan. That is if the index column order matches the order by clause the optimizer could retrieve the rows in index order and not sort them. Now the optimizer could be choosing to do this, but I doubt that this is the original poster's problem. I could misunderstand your reply, but I do not think that these two rules apply here. Received on Fri Nov 06 1998 - 12:20:33 CST