Re: How do I ensure order of rows loaded by SQL Loader?

puneet.bansal_at_wipro.com (Puneet) wrote in message news:<4c276d80.0311122127.562b3607_at_posting.google.com>...
> Hi everybody !
> I am loading a CSV file into Oracle 8i table using SQL Loader
> utility. The data is in such a format that the order of the records is
> of paramount importance. But when I retrieve the data from the table
> it can be in any order, isn't it?
>
> My doubts are:
> Does SQL Loader always load the records in the same order as that of
> the data file?
>
> If yes then how can I ensure that when I retrive the records they are
> always in the same order as the one specified in CSV file? (The table
> is just a temp table and it doesn't have any primary key or any column
> I can order by.) Will 'order by rowid' solve the problem?

Hi, Puneet,

SQL*Loader reads one line at a time in its datafile in that order. But what's loaded in a regular table is unordered. That's why a regular table is also called a heap table. If a later-loaded row can be fit in a hole in that heap, it may go there. Using direct-path load should help. But even then Oracle can't guarantee the data retrieved serially will be shown in the order as in the SQL*Loader datafile. As others suggested, you need a sequence number to order the data in Oracle. You wouldn't think you had this sequence in your CSV. But it *is* in there, implicitly by definition of a text file you read sequentially.

Using 'order by rowid' won't help either. Think of what's in a rowid and the concept of a heap table.

Yong Huang Received on Thu Nov 13 2003 - 08:07:12 CST