RE: Performance tuning

From: Javier Morales <Javier_at_infojobs.net>
Date: Wed, 8 Nov 2000 19:30:34 +0100
Message-Id: <10674.121462@fatcity.com>

ENGLISH: I will write in BILINGUAL english-spanish but, please, don't = take
it too serious... It's just a nice joke ;-) SPANISH: Voy a escribir en BILING=DCE ingles-espa=F1ol pero, por favor, = no lo
tomeis demasiado en serio... s=F3lo es una co=F1a graciosa ;-)

E: As I see, the only difference is the DECODE comparation values S: Tal y como veo, la =FAnica diferencia es la comparaci=F3n de valores = en el
DECODE E: What about if you mix the two select statements using that... S: Que tal si unes las dos consultas en una usando lo siguiente...

ENGLISH SELECT: 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', decode(lot_num_pro,'140','AC','PN'),
	 '12', decode(lot_num_pro,'140','AC','PN')=20
          '13', decode(lot_num_pro,'140','AC','PN') ) status,

PLA_IND_TIP_PLA FROM RECPLA, Suplot
WHERE PLA_IDE_EMP =3D'10004918'
and Pla_fol_lot =3D lot_fol_lot
and (lot_tip_lot =3D '12' or lot_tip_lot =3D '13' or lot_tip_lot =3D =
'0')

SPANISH SELECT: SELECCIONA 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,
decodifica(Pla_fol_lot,
           '0', decodifica(lot_num_pro,'140','AC','PN'),
	 '12', decodifica(lot_num_pro,'140','AC','PN')=20
          '13', decodifica(lot_num_pro,'140','AC','PN') ) estado,

PLA_IND_TIP_PLA
DE RECPLA, Suplot
DONDE PLA_IDE_EMP =3D'10004918'
Y Pla_fol_lot =3D lot_fol_lot
Y (lot_tip_lot =3D '12' o lot_tip_lot o '13' o lot_tip_lot =3D '0')

E: As my last advice... how about an index for PLA_IDE_EMP. S: Como =FAltimo consejo... que tal un =EDndice para PLA_IDE_EMP.

E: Don't worry, Marcos, I think your mistake was not lingual (but conceptual)... and I think it's no need for so much discussion!! ;-) S: No te preocupes, Marcos, Pienso que tu equivocaci=F3n no fu=E9 = idiom=E1tica
(sin=F3 conceptual) ... y creo que no hay necesidad de tanta = pol=E9mica!! ;-)

Regads,

---

 Javier Morales - javier_at_infojobs.net
 Administraci=F3n Bases de Datos Oracle - InfoJobs.net  Tel. +34 902 10 60 90 Ext. 2067
 Fax. +34 (93) 580 56 60
=20

-----Mensaje original-----
De: Marcos.Vera_at_msconsultores.com.pe
[mailto:Marcos.Vera_at_msconsultores.com.pe] Enviado el: mi=E9rcoles 8 de noviembre de 2000 17:17 Para: Multiple recipients of list ORACLE-L Asunto: 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 =3D'10004918'
and Pla_fol_lot =3D lot_fol_lot
and (lot_tip_lot =3D '12' or lot_tip_lot =3D '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 =3D'10004918'
and ( Pla_fol_lot =3D '0' OR
      Pla_fol_lot not in (select lot_fol_lot from suplot where =

(lot_tip_lot
=3D
'12' or lot_tip_lot =3D '13') )

     );

sorry about english ....

Markus ...

--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author:=20
  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 Received on Wed Nov 08 2000 - 12:30:34 CST