Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do I ensure order of rows loaded by SQL Loader?

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

From: Michael J. Moore <NOhicamelSPAM_at_comcast.net>
Date: Sun, 16 Nov 2003 16:58:17 GMT
Message-ID: <J0Otb.167960$ao4.543251@attbi_s51>


I know that this is a dead issue but I have to wonder why you did not follow Joel's advice and "normalize" the data. Maybe you had a good reason but too often I see people adding sequence numbers when what they should have done is reduced the data to 3rd normal form and created tables and constraints accordingly.

With your solution, your programmers will now have to code logic to detect if there are any 'associates' before attempting to delete a 'contact' - delete anomaly, and detect if there is a corresponding 'contact' before inserting an 'associate' - update anomaly. Also you now have issues of what happens when associates are delete leaving a gap in your sequence numbers.

Maybe your data is static so you don't have to worry about these problems. If so, you are home free. But if programmers need to maintain this data with updates, inserts and deletes, you have placed a heavy burden on them by not normalizing the data.

Regards,
Mike

"Puneet" <puneet.bansal_at_wipro.com> wrote in message news:4c276d80.0311140313.243de656_at_posting.google.com...
> Thank you Jim,John,Holger and Yong for showing me the right way. The
> problem is solved. In fact it is a neat little discovery (at least for
> me)!
> I've added one more column to the table to hold the sequence numbers.
> I defined a sequence and have defined one function to return
> appropriate sequence numbers:
>
> create function cdb_generate_sq(av_rowtype varchar2) return
> number
> is
> vn_sq_no number(14);
> begin
> if av_rowtype = 'CONTACT' then
> select sq_na_contact.nextval into vn_sq_no from dual;
> else
> select sq_na_contact.currval into vn_sq_no from dual;
> end if;
>
> return vn_sq_no;
> end cdb_generate_sq;
> /
> show errors
>
> And in the control file of the sql loader I've called this function
> while loading the data. My control file now looks like this
>
> options (errors=2000000)
> LOAD DATA
> replace
> INTO TABLE
> TEMP_CONTACT_EXCEL
> fields terminated by ","
> TRAILING NULLCOLS
> (ROW_TYP ,
> CNTCT_TYP_ID ,
> CNTCT_FIRST_NM ,
> CNTCT_LAST_NM ,
> CNTCT_MID_INIT ,
> SEQ INTEGER "cdb_generate_sq(:ROW_TYP)"
> )
>
> Note the last line.
> This inserts the sequence numbers as it loads the records based on the
> value of the ROW_TYP column which can either be 'CONTACT' or not. Now
> while accessing the records I can order them by sequnece numbers and
> be sure that I get them in the correct order.
> This was an eye opener for me and I think many others may not know
> about this that's why I've shared this with everybody.
>
> Puneet.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> yong321_at_yahoo.com (Yong Huang) wrote in message
news:<b3cb12d6.0311130607.154b5f08_at_posting.google.com>...
> > 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 Sun Nov 16 2003 - 10:58:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US