2

I have to use Java POI to batch import some excel files into Oracle database. The Java program is very easy, just use JDBC to inert them.

But when I checked the table, I found the physical orders of data changed. For example, I import data like this:

S/N     Name       
S0001   Name1
S0002   Name2
S0003   Name3
S0004   Name4
S0005   Name5
S0006   Name6
.....

and the table is like that:

S/N     Name       
S0001   Name1
S0003   Name3
S0004   Name4
S0002   Name2
S0006   Name6
S0005   Name5
.....

Have anyone got the same strange issue before?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
superleo
  • 319
  • 1
  • 2
  • 10

1 Answers1

8

A heap organized table is inherently unordered. Unless your query specifies an ORDER BY clause, Oracle is free to return data in whatever order it chooses. And it is free to physically store data in any order regardless of the order of inserts.

If you care about the order in which data is returned, you can add an appropriate ORDER BY, 

SELECT serial_number, name
  FROM your_table_name
 ORDER BY serial_number
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • and if there are many rows, an index (unique or not) on `serial_number` will make it faster. – Benoit Nov 11 '11 at 07:33
  • 1
    @benoit - actually having an index on the sort criteria trades `consistent gets` for `sorts`. Which appraich is faster depends on various factors. – APC Nov 11 '11 at 10:27