Re: query optimization - ignore the previos one

Ignacio Galmarino wrote:
> sorry if this is a newbe question
>
> i have this query
>
> select PAGO.*, PAGOTELEFONO.*, REVERSION.* from REVERSION,
> PAGOTELEFONO, PAGO, NOTA, CAJA where 75 = CAJA.IDRECAUDADOR AND
> CAJA.IDCAJA = NOTA.IDCAJA AND NOTA.IDNOTA = PAGO.IDNOTA AND
> PAGOTELEFONO.IDPAGO = PAGO.IDPAGO AND REVERSION.IDPAGO = PAGO.IDPAGO
> AND REVERSION.FECHAREVERSION >= to_date('20041025','yyyymmdd') AND
> REVERSION.FECHAREVERSION <= to_date('20041025','yyyymmdd')
>
> with idRecaudador = 77 the query last 5 seg and return 2 row
>
> with idRecaudador = 75 the query last 1 min 7 seg and return 1 row
> !!!!
>
> any idea whats happening ??? why this response time difference only
> changing 1 parameter ???
>
> thanks
> Igancio

It is hopeless trying to guess the answer. We need an Oracle version at the minimum. And even with that, the only way anyone can say with any certainty why there's a difference is to look at the explain plans involved in each query.

Perhaps your table statistics indicate a low cardinality for 77, and a relatively high one for 75. So for one it does an index access, and for the other it does a full table scan?

But honestly, who can say without any actual evidence?

Do you know how to generate execution plans? Or, if you have 9i, how to query v$sql_plan?

Regards
HJR Received on Tue Nov 30 2004 - 16:42:56 CST