Home » SQL & PL/SQL » SQL & PL/SQL » Which Approach is the Better (oracle 10g,windows)
Which Approach is the Better [message #300113] Thu, 14 February 2008 04:56 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

The below is Psedudo code:

EMP table contains
Empno Deptno
1     10
2     20 

Dept table contains
DEPTNO  DEPTNAME      DEPT_ACT_DT
10      ACCOUNTS      10-01-2006
20      STATIONARY    10-01-2007
10      ACCOUNTS      10-01-2007


Req: My requirement is to display the empno,deptname of that employee and the minimum date of the deptno.

i.e.,EMPNO , DEPTNAME and MIN(DEPT_ACT_DT)

Approach-1:By Join these two tables and by using the group by clause i can achieve this.
Approach-2: By writing two different functions and use this function in the select clause we can achieve this
i.e.Below is the Pseudo code,
Functio f1 is:
create or replace function f1(prm_deptno varchar2) return varchar2
      v_dname varchar2(100);
      begin      
      select deptname into v_dname  from dept where deptno =prm_deptno;
      return(v_deptname);
      end;


Function f2:
create or replace function f2(prm_deptno varchar2) return varchar2
      v_dname_DACT varchar2(100);
      begin
      select min(DEPT_ACT_DT) into v_dname_DACT  from dept where deptno =v_deptno;
      return(v_dname_DACT);
      end;

Use these functions in the select clause like:

select empno,f1(deptno)dname,f2(deptno)dept_act_dt from emp ;
But, the Problem in this Approach is that I am refering the same table two times in the f1 and f2 functions.
Is it Possible to send the two values at once ?.
If yes then How ?


Note:
These tables contains the Millions of records. 
In this case which Approach is the Best and Improve the Performance.

And Finally IS there any Other/Better Approach ?


Thanks for the Help,




My Requirement is

[Updated on: Thu, 14 February 2008 05:51] by Moderator

Report message to a moderator

Re: Which Approach is the Better [message #300125 is a reply to message #300113] Thu, 14 February 2008 05:19 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
why create two functions when it can be easily done with 1 sql statement?


regards,
Re: Which Approach is the Better [message #300126 is a reply to message #300113] Thu, 14 February 2008 05:22 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Yes, you are right,

I am also thinking the same thing to do the same.
But,If the tables contains the millions of rows then
Using the Group by clause will hamper the performance,right?
So, I wnat to know is the any other way other than what i Mentioned.

Thanks for the Help.
Re: Which Approach is the Better [message #300132 is a reply to message #300126] Thu, 14 February 2008 05:43 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:
Using the Group by clause will hamper the performance,right?

how did you come to this conclusion?how about running the EXPLAIN plan ?



regards,
Re: Which Approach is the Better [message #300134 is a reply to message #300113] Thu, 14 February 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no problem with SQL.
Oracle is designed to handle billion rows.

Regards
Michel
Re: Which Approach is the Better [message #300153 is a reply to message #300134] Thu, 14 February 2008 07:42 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Furthermore, using PL/SQL tends to give a worse performance then only using SQL. So, trying to avoid performance issues, chances are that you actually made them worse...

So, back to the original question: try to do it using only SQL and check how it performs.
Previous Topic: IN vs. EXISTS
Next Topic: daily count for last two years.
Goto Forum:
  


Current Time: Sat Dec 03 05:44:30 CST 2016

Total time taken to generate the page: 0.08617 seconds