Home » SQL & PL/SQL » SQL & PL/SQL » What would be the best solution to copy/process the data?
What would be the best solution to copy/process the data? [message #313947] Tue, 15 April 2008 05:14 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,

Existing code is much more complicated (a single SQL query that is several thousands characters long and is evaluated as EXECUTE IMMEDIATE), but it is similar to the code bellow:
create out_table as
 select
  id,
  max(date1),
  max(date2),
  max(date3),
  max(date1)+max(date2)+max(date3)
 from in_table
;


  1. Number of "dates" can vary so this is a dynamically created statement - in this example we had 3 input dates, but we can have 100 of them as well. Thus we don't know what are the column names (but we can say that this is always date<N>
  2. max(dateN) is evaluated more than once - can we re-use it so it is evaluated only once? Or is this handled by the caching mechanism?
  3. I need to create additional column which contain sum of max(dateN) where max(dateN) fits to some pattern (from another table), ex. where max(dateN)>5. How can this be implemented in an easy way?
  4. Would it be logic to replace SQL query with PL/SQL anonymous function?


Thanks in advance
Re: What would be the best solution to copy/process the data? [message #314153 is a reply to message #313947] Tue, 15 April 2008 22:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You don't need to worry about specifying MAX(col) 2 or more times. Even if Oracle calculates it separately each time, there is no material overhead in a simple aggregate MAX(). You would struggle to measure a fraction of a second difference over millions of rows.

For your sum, is there anything wrong with
CASE WHEN MAX(date1) > 5 THEN MAX(date1) ELSE 0 END + ...


I don't understand what you mean about the Anonymous Function. Can you give an example.

Ross Leishman
Re: What would be the best solution to copy/process the data? [message #314238 is a reply to message #314153] Wed, 16 April 2008 02:40 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
The thing is that existing code is a procedure which is generating a long string (SQL query which in theory can be even 40000 characters long) and is called with a parameter. This "sum > 5" is an example condition, but depending on the input other condition could be used.
This code is executed once in a week, but is used for many input tables, so the procedure is executed many times and is processing data from the database that is about several GB large - this is why I worry about the performance.
About the anonymous function: I was thinking about something like:
Declare
date_1 number;date_2 number;date_3 number;
for in_row in in_table; loop
date_1=max(date1);date_2=max(date2);date_3=max(date3);
insert into out_table values (
 date_1,
 date_2,
 date_3,
 sum(date_1,date_2,date_3),
 sum(...)
);
end loop;

Re: What would be the best solution to copy/process the data? [message #314252 is a reply to message #314238] Wed, 16 April 2008 03:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Splitting a pure SQL routine across SQL and PL/SQL is almost always slower. The only time it makes sense is when the SQL too complex and unwieldy to maintain, and where you can exploit PL/SQL capabilities such as iteration optimise an access path.

If the SQL is too big and unwieldy, then maybe this applies; but you would need to replace it with something efficient using BULK COLLECT and FORALL loops.

I don't have any better advice for you.

Ross Leishman
icon14.gif  Re: What would be the best solution to copy/process the data? [message #314253 is a reply to message #314252] Wed, 16 April 2008 03:09 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks
Previous Topic: how to add hour and minute
Next Topic: How do I create a mat view with zero length columns ?
Goto Forum:
  


Current Time: Wed Dec 07 04:51:31 CST 2016

Total time taken to generate the page: 0.14090 seconds