Oracle does not maintain order of inserted records
I need to have records in table B sorted with regard to the primary key. But Oracle has the problem of not maintaing the order in the way records were inserted.
I am facing a pretty unusual problem. I have two tables, A and B. When I insert some value in table A, a trigger will be initiated and the same sum of the column values will be populated in table B from table A. At a time 5,000 records are inserted in table A and hence, similarly in table B. The requirement is to have records in table B sorted w.r.t the primary key. But, Oracle has the problem of not maintaing the order in the way records were inserted. So, the approach that I followed is:
1. Create clustered index (i.e., index organized table) in both the tables w.r.t their primary key. Then I performed insertion. It worked fine -- the order of insertion was the same as the order of storing the records in both the tables (table B being populated by trigger). But I faced some other issue here. When I delete some record from table B and then insert a record, the order of insertion was not maintained. The records become haphazardly stored. I want the records to be appended after the last record.
2. To resolve the issue I faced in Step 1, I tried to use the direct-path insert method which says that the records to be added in the table will be appended to the last record present in the table. For this, I modified the trigger (which is responisble for populating table B) to use /*+Append*/ option. But still this doesn't work.
Can anyone provide me with the solution? Please don't tell me to use the "Order By " clause while fetching records from Table 2, since fetching the record is not in my control (records are fetched and deleted by BEA control -- and it needs the ordered records). The only thing I can do is to insert the record in proper order in the table.
