Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

data import using SQLLDR

Status
Not open for further replies.

rk68

Programmer
Jul 15, 2003
171
IN
Hi,
I have text data of around 1 million records.
Is the data imported into oracle in the same order as in the text file. I'm using SQLLDR to import data.

TIA,
Raj
 
The date will be imported in the order of the sequential file. HOWEVER, remember that if Oracle does not have an index on the table, the data (when retrieved by a SELECT) could be in any order at all. I have generally observed that the data comes out in the same order that it was put it UNTIL changes are made to the table. Then, it's anything goes.

If you want the data to come out in a certain order, add an index. This can also include adding a row number during the load process. Using an index for ORDER BY is THE ONLY WAY to guarantee that the data will come out in a certain order when you use a SELECT statement.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


 
since you are using oracle 11, why don't you just use an external file and then you can query the data directly from the csv file in any order that you want

Bill
Lead Application Developer
New York State, USA
 
Thanks johnherman & Beilstwh.
I just wanted to check this out, bcoz in case while importing the data into oracle & anything goes wrong, the data is partially imported.
So the only option left is to delete all the data imported & redo the import process.
 
So you're saying that it only imports good data that is clean and correct? That seems like a good thing. [bigsmile]

There's a directive you can give to SQL Loader to put BAD records that don't load into a separate file ("[tt]BADFILE 'filename.bad'[/tt]"). That can let you address just those problems and then re-attempt the load with the smaller batch. Doing it that way does load a huge bulk of good records, and also separate the (hopefully) smaller number of exceptions that you have to handle manually. That can save a lot of work.

And, as Beilstwh says, you can leave it in an external CSV file, but that introduces a whole other set of problems. Complex joins with the data can be extremely slow, and those BAD records that aren't loading can really break that external data, sometimes in ways that you don't even know you have a problem. It can be a huge pain to debug why you aren't seeing all of your data.

If data integrety is a concern, it is best to load it.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top