RE: Performance tuning

From: <Marcos.Vera_at_msconsultores.com.pe>
Date: Wed, 8 Nov 2000 11:14:20 -0500
Message-Id: <10674.121432@fatcity.com>

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 .... Received on Wed Nov 08 2000 - 10:14:20 CST