Re: Insert without a Primary Key

Stan Novinsky wrote:
>
> A question:
>
> 150 rows of data are inserted into a table without a primary key.
>
> When a select is executed on the table, will the 150 rows of data be
> retrieved
> in the same order as inserted - (ie. row 1 inserted into the table
> table will always be row 1 retrieved.)
>
> Thanks

As all of the responses state, no, you cannot guarantee that the rows will be returned in the same order.

Think of how oracle processes an insert. It looks for a block on the free list and fills it. It then gets the next block on the free list. That block could be physically before the block that was just used.

Now think of how Oracle handles a full table scan. It scans through the blocks in order and returns the data.

This explains why a newly created table would likely return the rows in order, because Oracle used the blocks in sequence. However, as you perform deletes and updates, Oracle will begin inserting rows all of the place, so your data won't be returned in order.

-- 
Bob Swisshelm
Eli Lilly and Company
nospam.swisshelm_at_lilly.com  
(remove 'nospam' if you want to send mail to me)