Re: return full recordset and sum of recordset

i'm not sure I understand your answer.

i am about to write the oracle stored procedure as 2 select statements both effectively selecting the same thing but one of them selects the SUM into an out parameter while the other selects all the records into an out ref cursor.

this is in fact how a similar procedure has been written in our company. however coming from a SQL server background that is not the way I would do it because all the complex joins etc are being done twice and also if it needs changing in the future the developer would need to change both selects "in synch" which in my experience is where mistakes are made and the cost of maintenance increases. I would do the complex joins once into a temporary table and then use selects on the temporary table without any criteria for the SUM and the REF CURSOR output parameters.

So are you saying that a complex select with many joins and non-indexed criteria will perform as quickly as a select all from a temporary table? Or that the fact that I have just done the "select SUM..." means that the next "select *..." will be cached and therefore have little impact?

what does "select from a result set..." mean? Sorry to be thick. I did Oracle years ago but have been SQL Server man for the last few years and don't really know all the ins and outs of Oracle.

thanks
Phil Received on Tue May 17 2005 - 02:37:07 CDT