Re: re-ordering rows

Arjan,

You are correct that Oracle never guarantees the order of rows being returned for the database unless an ORDER BY is used. The only exception to this, is if you are capable of retrieving the rows by a INDEX RANGE SCAN (according to the execution plan), in which case, the rows are returned in the correct order.

Andrew

Arjan van Bentem wrote:

> John Higgins wrote
> > So why would anyone want to physically order the rows within a
> > table? To improve the performance of a non-unique (multi-row)
> > query. Like, "SELECT * FROM A WHERE X = "ABC";"
>
> Hmmm, I always thougt that "Oracle does not return the rows in a particular
> order" also meant "Oracle does not STORE the rows in a particular order". And
> even when it does, I always thought that DBA activities might change the
> physical order again, so a developer should never rely on it. But since the
> thing you describe seems to be common practice I believe you're right. Thanks!
>
> I still doubt that Gennady meant to achieve the thing you describe, so again
> Gennady, like John pointed out as well: please understand that Oracle only
> guarantees any ordering if you include a order by in your select.
>
> Arjan.
Received on Sun Apr 25 1999 - 07:29:29 CDT