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

[Ryan Wagner]

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.

Example: If I insert records with employee numbers 1 throughy 100,000 into
the oracle database in ascending order, then the first record viewed after
inserting all records should be record with employee number1. The last
record should be record with employee number 100, 000.

thanks.

Ryan

[D Rolfe]

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

[Witold Andrzejewski]

Ryan Wagner wrote:

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.

Example: If I insert records with employee numbers 1 throughy 100,000 into
the oracle database in ascending order, then the first record viewed after
inserting all records should be record with employee number1. The last
record should be record with employee number 100, 000.

thanks.

Ryan

Making Oracle store data in an order you want is impossible. The closest
solution would be to create an index or an index organized table. Oracle
will then use the order in index to optimize the ORDER BY.

Witold Andrzejewski