Re: re-ordering rows

Where did I say anything about the order of rows returned by a select from the table?

The requestor asked how to physically order the rows within the table. This is a way to do that.

As you point out, the retrieved order is unknowable if unspecified by an order by. 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";"

An index on column X will point to every row that qualifies. If the rows are randomly inserted into the table, the index may point to many blocks. However, if the rows are physically ordered, all the selected rows will be "clustered" into a smaller set of blocks -- resulting in fewer I/O for the query. Note: since I didn't use an ORDER BY, the retrieved rows are still not guaranteed to be in any particular sequence.

Arjan van Bentem wrote:

> John Higgins wrote
> > Create table temp_A as select * from A order by x,y,z;
>
> I really disagree.
>
> I know that when your table has indexes on x, y and z Oracle might return the
> rows in a particular order if you only select those very columns without any
> join to another table. But as far as I know Oracle never promises you in what
> order the rows will be returned, unless you explicitly add an order by clause.
>
> You simply cannot rely on the assumption that Oracle returns the rows in the
> very same order in which you inserted them!
>
> Arjan.
Received on Sat Apr 24 1999 - 09:20:51 CDT