Ask the Expert

Oracle does not maintain order of inserted records

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.

    Requires Free Membership to View

No relational database can guarantee order in a table. That is precisely why the ORDER BY clause was created for your SELECT statements. In my entire DBA career, I have never had to care about the physical ordering of rows within a table. If I wanted to pull the data out in a particular order, I use the ORDER BY clause. If you consult other experts in relational database design, they will tell you to rethnk your design. And that is my suggestion to you as well. That being said, if you still feel you need this requirement, then I would point you towards the Indexed Organized Table data structure, which will maintain a sort order based on the index key.

This was first published in May 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: