0

If I use an sql bulk loader to load 10 000 rows into an oracle database from a flat file, will the rows be inserted in the same order as they appear in the file? For example, let us say there is a unique key column that is automatically incremented by 1 for every row inserted.

So can I predict that the first row in the file will become the first row in the db, (a.k.a have a value of 1 for this column)

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
Victor
  • 16,609
  • 71
  • 229
  • 409

2 Answers2

2

Does "sql bulk loader" mean "SQL*Loader"? Or external tables? Or some other technology for doing bulk data loads (i.e. Toad's import mechanism or SQL Developer's import mechanism)?

The simplest way to end up with "out of order" data would be for your bulk load to be processed in parallel. That's one of the reasons that external tables are so useful-- you can easily specify a degree of parallelism for the load (given some constraints, of course) so that your large loads can be run more quickly. Other bulk load tools may have similar options or they may always do their loads in a single session. If the load is being done in a single session, it's highly likely that the tool would read the data from the file in order and insert it in order.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Just to add to your statement, Parallel loading , means running multiple sql loader instances concurrently, when the loading has to Be APPEND always. And it is natural that, the order goes out of sync. – Maheswaran Ravisankar Jun 13 '14 at 04:24
  • 1
    @OracleUser - That's the approach that SQL*Loader uses. External tables can parallelize loads by automatically splitting a large data file into multiple chunks without the human running multiple SQL*Loader instances. That's one of the reasons that external tables are more powerful. – Justin Cave Jun 13 '14 at 04:39
  • yes, justin, But I have a feeling, ORACLE_LOADER is derived from sqlloader?! Just it is enhanced for developers. – Maheswaran Ravisankar Jun 13 '14 at 04:47
  • @OracleUser - Sure, external tables are built on top of SQL*Loader. And, sure, it's just layering additional functionality on top of SQL*Loader. In this case, it's adding the ability to run loads in parallel without manually spinning up a bunch of SQL*Loader instances. – Justin Cave Jun 13 '14 at 05:00
1

You cannot rely on load order to be consistent with source file order. Don't be fooled when some samples end up that way, it's not guaranteed.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • Citation to a theoretical explanation could be very useful.! Though I wont beleive the order preservation. The order is preserved in almost all my cases. with huge records too. Havent researched much though. Especially, the discard and bad files, always have the same order.!! – Maheswaran Ravisankar Jun 12 '14 at 21:30