Re: Problems with a trigger

Hi Heggelund!

You are right, of course.
It was just an attempt to generate a sorted list of rows into a table. Now we use a simple ascii file...

Thanks!

-- 
Andrea Pallotta
DIGITAL Italy
Gruppo di Sviluppo ( Development Team )

Heggelund <d92hegge_at_ix_prod.hfk.mil.no> scritto nell'articolo
<1997Mar21.122639.20730_at_ix_prod.hfk.mil.no>...


> 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.

> 

>