Q

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.

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close