RE: Performance tuning

From: <Yosi_at_comhill.com>
Date: Wed, 8 Nov 2000 12:46:02 -0500
Message-Id: <10674.121452@fatcity.com>

Markus,

Your best bet seems to me to be to write a function to get the status, since that seems to
be the only column thats different in the two selects. For example, write the function
get_lot_status (pla_fol_lot) which will go to the suplot table and return the status
(AC or PN) for a given pla_fol_lot, depending on its lot tip lot.

With that, you'll avoid the union all, only do one query, and that query will be faster.

I even coded it, in a weak moment. No guarantees here, I didn't test it at all but I
think I'm close:

Start Here CREATE OR REPLACE FUNCTION get_lot_status (p_pla_fol_lot NUMBER) RETURN VARCHAR2 IS RetVal VARCHAR2 (2);

BEGIN    SELECT DECODE (lot_num_pro, '140', 'AC', 'PN')    INTO RetVal
   FROM suplot
   WHERE lot_fol_lot = p_pla_fol_lot
   AND lot_tip_lot in ('12', '13');

RETURN RetVal;

EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN

      SELECT  DECODE (p_pla_fol_lot, '0', 'PN', 'AC')
      INTO    RetVal
      FROM    DUAL;

      RETURN RetVal;

END; END;
-- End of function

Then, simply:

SQL Code SELECT pla_fol_lot, pla_fol_pla, pla_per_dev_rem, pla_ide_emp, pla_fec_caj, pla_tot_obl_inf, pla_tot_vol_inf, pla_tot_apo_emp_inf, pla_tot_apo_seg_inf, pla_tot_com_por_inf, pla_per_dev_rem, get_lot_status (pla_fol_lot) status , pla_ind_tip_pla FROM recpla WHERE pla_ide_emp ='10004918' /
End of SQL Code

HTH, Yosi

> -----Original Message-----
> From: Marcos.Vera_at_msconsultores.com.pe
> [mailto:Marcos.Vera_at_msconsultores.com.pe]
> Sent: Wednesday, November 08, 2000 11:17 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Performance tuning
>
>
>
>
>
>
> hi gurus ....sorry im trying to optimize this query ...
> unfortunately it takes nearly 20 seconds .....(it's a lot) ...
> recpla --> 800000 records
> Suplot --> 100000 records ...
>
> SELECT PLA_FOL_LOT,
> PLA_FOL_PLA,PLA_PER_DEV_REM,PLA_IDE_EMP,PLA_FEC_CAJ,PLA_TOT_OBL_INF,
> PLA_TOT_VOL_INF,PLA_TOT_APO_EMP_INF,
> PLA_TOT_APO_SEG_INF,PLA_TOT_COM_POR_INF,
> PLA_PER_DEV_REM,decode(lot_num_pro,'140','AC','PN') status
> ,PLA_IND_TIP_PLA
> FROM RECPLA, Suplot
> WHERE PLA_IDE_EMP ='10004918'
> and Pla_fol_lot = lot_fol_lot
> and (lot_tip_lot = '12' or lot_tip_lot = '13')
> union all
> SELECT pla_fol_lot,
> PLA_FOL_PLA,PLA_PER_DEV_REM,PLA_IDE_EMP,PLA_FEC_CAJ,PLA_TOT_OBL_INF,
> PLA_TOT_VOL_INF,PLA_TOT_APO_EMP_INF,
> PLA_TOT_APO_SEG_INF,PLA_TOT_COM_POR_INF,
> PLA_PER_DEV_REM,decode(pla_fol_lot,'0','PN','AC') status
> ,PLA_IND_TIP_PLA
> FROM RECPLA
> WHERE PLA_IDE_EMP ='10004918'
> and ( Pla_fol_lot = '0' OR
> Pla_fol_lot not in (select lot_fol_lot from suplot
> where (lot_tip_lot =
> '12' or lot_tip_lot = '13') )
> );
>
> sorry about english ....
>
> Markus ...
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Marcos.Vera_at_msconsultores.com.pe
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Nov 08 2000 - 11:46:02 CST