I am facing a pretty unusual problem. I have two tables, A and B. When I insert some value in table A, a trigger...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.