Re: I need records to be in the same order as they were inserted in

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Tue, 08 Feb 2005 08:10:34 +0000
Message-ID: <420873FA.9000801_at_orindasoft.com>


Ryan,

> I am doing many SQL insert statements to insert records into an oracle
> database. I need the order of the records, after inserting all records, to
> be the same as the order I inserted them in. Right now my program fails to
> keep the records in order if the number of records is large. I inserted
> 100,000 records and the first record after all inserts were done was
> record#540. I have been told that the /*+ APPEND */ hint and /*+ PARALLEL */
> hint can accomplish this. Is this correct? I want to avoid using ORDER BY at
> all costs as its slow and I can't affort the slowdown. Has anyone else had
> this problem? and can help.
>
Stop messing about with hints and use an Order By. One of the fundamental rules of relational databases is that the order rows are physiclly stored is not something you control directly. If your data is inherently ordered (e.g. a top 10 list) and doesn't have something you can sort by (e.g. family name. employee number) then add a number column and use an Oracle sequence to assign an order to records.

Where did you get the idea that Order By would be slow?

You should make a trip to your local bookstore and buy (or failing that read!) a book on Relational Database theory.

David Rolfe
Orinda Software
Dublin, Ireland Received on Tue Feb 08 2005 - 09:10:34 CET

Original text of this message