Re: Problems with a trigger

Hi,

you cannot force Oracle to store the data ordered. What probably happens here is that when you add more rows to "two" you allocate a new extent. This extent may be placed anywhere in the tablespace (where if finds space). When you read the table via full tablescan it will read the extents sequentially and you have no garantee that this will return data in the same order as you inserted them.

This is excactly how the relational theory specify it to work, you cannot base your programs logic on the data apparing in any order without you forcing it with a sort in the retrieving cursor. So there are no point in using your table "two" in a relational database as Oracle.

Rgds
Steinar Heggelund

Andrea Pallotta (anpallotta_at_flashnet.it) wrote:
: Hi there!!!
 

: I have this problem with a trigger.
: The trigger i've wrote should fill a table (called "two") with a sorted
: list based on another table (called "one" ).
: The list in "two" will be recreated every time a new row is inserted in the
: first table "one".
: It works fine... but only with few rows ( 100-150 )
: if i test the trigger with a loop like this, the rows in "two" are not
: sorted ...
 

: Some suggest to solve this problem??
 

: This is the trigger
: declare
: cursor rows is
: select * from one order by 1,2,3;
: row rows%rowtype;
: begin
 

: for row in rows loop
: insert into two values (a,b,c,d);
: end loop;
 

: this is the test loop.
: begin
 

: for i in 1..1000
: insert into one values (a,b,c,d);
: end loop;

: Thanks in advance
 

: Andrea Pallotta
: Gruppo di Sviluppo
: Digital Italia

--

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.