Join Vs Function

From: SATYA PAL <spgangwar_at_yahoo.com>
Date: 7 Sep 2001 08:10:41 -0700
Message-ID: <a7b8a58b.0109070710.133616e7_at_posting.google.com>

Hi There,

I have a requirement to get the Company name five times in a query. So to get the company name, i have to join the company table five times in my sql. There are two ways to achive the above requirement:
- Join the company five times

• another option - is to define a function and pass the company number to get the name

SQL Using Join

SELECT

  FMS_BT_EQP.BT_SEQ, 
  FMS_BT_EQP.LOC_SEQ, 
  FMS_BT_EQP.CO_SEQ_MFC, 
  FMS_BT_EQP.CO_SEQ, 

  OWNER.CO_SHORT_NAME,

  FMS_BT_EQP.CO_SEQ_MAINT_BY, 
  MAINTAINER.CO_SHORT_NAME,
  FMS_BT_EQP.BT_ID, 
  FMS_BT_EQP.BT_NAME, 

FROM
  FMS_BT_EQP,
  IBIS_CO OWNER,
  IBIS_CO MAINTAINER
WHERE
  (FMS_BT_EQP.CO_SEQ_MAINT_BY = MAINTAINER.CO_SEQ (+))   AND (FMS_BT_EQP.CO_SEQ = OWNER.CO_SEQ (+))   AND FMS_BT_EQP.BT_ID = ‘BoatId';

SQL Using GetCompanyFunction

SELECT

  FMS_BT_EQP.BT_SEQ, 
  FMS_BT_EQP.LOC_SEQ, 
  FMS_BT_EQP.CO_SEQ_MFC, 
  FMS_BT_EQP.CO_SEQ, 
  GETCOMPANY(FMS_BT_EQP.CO_SEQ), 

  FMS_BT_EQP.CO_SEQ_MAINT_BY,
  GETCOMPANY(FMS_BT_EQP.CO_SEQ_MAINT_BY),   FMS_BT_EQP.BT_ID,
  FMS_BT_EQP.BT_NAME,
FROM
  FMS_BT_EQP
WHERE
  FMS_BT_EQP.BT_ID = ‘BoatId';

In terms of the performance, which is the faster? If someone can help me on this, would be a great help for me. because i have number of instances where i can achieve the requirement by defining the functions instead of having multiple joins in SQLs.

Thanks in Advance
Satya Received on Fri Sep 07 2001 - 17:10:41 CEST