Re: +Sort hint

Sure.

But note: pre-sorting on a set of columns that ARE to be non-unique indexed. When loading, (assuming a default load with no "tricks"), all the rows with the same key value will be on the same block or a set of contiguous blocks.

Note that inserts will write data in a contiguous format, it's the select without index use that is not guaranteed to retrieve them in physical order. This is due to the possibility that blocks of that table might already be in the cache, being therefore the first ones to be read, rather than the physical start of the table.

Once you retrieve using the index, one physical read will get you a bunch of rows of the same key values into the buffers. Next read (of the next duplicate key) will get them off the buffers, not the disk.

If you don't pre-sort, then there is no guarantee that rows with same key values on the non-unique index will be contiguous. When retrieving (using the index) all the rows of a certain key value, you may be doing "hidden" read all over the disk to get the required rows.

So the pre-sorting works to reduce I/O by helping a read-by-index. In a normal sequential table scan, you have no guarantee whatsoever that rows will come out in a certain order.

--
Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
Is there a nospam domain?
http://www.users.bigpond.net.au/the_Den

Doug Cowles <dcowles_at_bigfoot.com> wrote in message news:3783F91C.BC010C70_at_bigfoot.com...
> Got the idea..but I thought no matter what order you put data in,
> it wouldn't neccessarily come out that way. Does this mean that
> it is actually stored in the order it is inserted, but not necessarily
> extracted the same way? I've heard a couple of times when people
> want the first 10 rows of a table with rownum for example, that the
> first 10 rows could vary from day to day...
>
> Thanks,
> Dc..
>
Received on Thu Jul 08 1999 - 05:46:25 CDT