Home » SQL & PL/SQL » SQL & PL/SQL » Data stored in DB is not in the same order as they were inserted
Data stored in DB is not in the same order as they were inserted [message #236732] Thu, 10 May 2007 14:10 Go to next message
mac1sam
Messages: 1
Registered: May 2007
Junior Member
Hi,

I am facing an issue w.r.t the ordering of inserted records (around 5000 records at a time) in the table. The scenario is: I have a table B which is populated by a trigger (applied on Table A) after the records are inserted in table A. The trigger looks like:

create or replace TRIGGER trig Insert ON tableA FOR EACH ROW
DECLARE temp integer;
Begin
select tableB_SEQ.NEXTVAL into temp from DUAL;
insert into tableB(col1,col2,col3, primKeyCol)
values (tableAcol1, tableAcol2, tableAcol3 ,temp);

Table B is used by BEA Weblogic internally. So, my job is only to place records sorted by primary key into table B. Fetching / Deleting of records from table B can be done only by BEA internally as I mentioned already ( So, please don't propose the solution of using "ORDER BY" option while reading from table B ... because BEA uses the table ....and its not in my control)

One of the solution that came into my mind is to use ORGANIZATION INDEX. I did that. I created table B with ORGANIZATION INDEX option. As a result, records in Table B were stored in Primary Key order. Perfectly fine. But as I already told, BEA uses this table. It reads the record one at a time, and after reading it, deletes the record from the table. Now the issue arises. As long as u keep on keep on inserting the records in the table, the insertion order is maintained. But when any deletion is performed (either manually / by BEA), and then new records are inserted, the ordering of records are gone.

To resolve this issue, I tried to used Direct-Path Insert option (SERIAL / PARALLEL) in the trigger query. But this also doesn't work.

Can anyone propose the solution for this issue. I really need the solution.

Thanks,
Meghanth Sharma
Re: Data stored in DB is not in the same order as they were inserted [message #236733 is a reply to message #236732] Thu, 10 May 2007 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is none.

There is an order if and only if there is an "order by" clause.
Otherwise there is no order.

Regards
Michel
Re: Data stored in DB is not in the same order as they were inserted [message #236817 is a reply to message #236732] Fri, 11 May 2007 01:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you mean that this table is used by WL?
WL itself does not use any database table, as far as I know, only applications that run on WL.
Previous Topic: ERROR: ORA-01017
Next Topic: not a valid month error
Goto Forum:
  


Current Time: Sat Dec 03 15:49:41 CST 2016

Total time taken to generate the page: 0.13575 seconds