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.

"Ryan Wagner" <ryan.wagner (AT) sympatico (DOT) ca> wrote

Oracle controls how the records get physically stored on disk and depending
upon the nature of the records it may not physically store them in the same
order on disk as they were inserted. (a lot would depend upon pctfree and
pctused) In addition, in SQL standards the order the records are returned
to you are unspecified unless you specify what order. It does not have to
have anything to do with the physical order and it may not be consistent
from query to query. You must use order by to specify what order you want
the records to be returned to you in. Yes, if you want 100,000 records
returned to you it may take awhile to sort them into a particular order.
You might try creating an index on employee number asc and specifying in
your query or session the first_rows hint.

Don't rely on a "default" order or an unspecified order. It will bite you
in the rear end.
Jim

On Mon, 7 Feb 2005, ryan.wagner (AT) sympatico (DOT) ca wrote:
select *
from tbl
order by employee_nbr
;

--
Galen deForest Boyer