Re: Slow query with Oracle 10G

<slygagnon247_at_gmail.com> schreef in bericht news:1187704220.729587.147640_at_o80g2000hse.googlegroups.com...
> I would like know if the function NVL can be used in Oracle 10g?
> Do there exist operational limits of function NVL?
> Does function NVL slow down the speed of execution?
Unable to answer either of these question.

> CURSOR Cur_Request IS
> SELECT *
> FROM TABLE1
> WHERE FIELD1 = NVL(p_no_inter_relie, p_no_inter)
> AND FIELD2 >= p_dt_deb_extrac
> AND FIELD3 <= p_dt_fin_extrac
> AND NVL(FIELD4,'0') = NVL( FIELD12, NVL(FIELD4,'0'))
> AND NVL(FIELD5,'0') = NVL( FIELD13, NVL(FIELD5,'0'))
> AND NVL(FIELD6,0) = NVL( FIELD14, NVL(FIELD6,0))
> AND NVL(FIELD7,'0') = NVL( FIELD15, NVL(FIELD7,'0'))
> AND NVL(FIELD8,'0') = NVL( FIELD16, NVL(FIELD8,'0'))
> AND NVL(FIELD9,'0') = NVL( FIELD17, NVL(FIELD9,'0'))
> AND NVL(FIELD10,'0') = NVL( FIELD18, NVL(FIELD10,'0'))
> AND NVL(FIELD11,'@') = NVL( FIELD19,NVL(FIELD11,'@'));
Blindly following your assumption I'ld first replace the (nested) NVLs

   AND NVL(FIELD4,'0') = NVL( FIELD12, NVL(FIELD4,'0'))    AND NVL(FIELD5,'0') = NVL( FIELD13, NVL(FIELD5,'0')) by COALESCE

   AND COALESCE(FIELD4,'0') = COALESCE( FIELD12, FIELD4,'0'))    AND COALESCE(FIELD5,'0') = COALESCE( FIELD13, FIELD5,'0')) Then cut the number of coalesces in half by rewriting the lines to

   AND ( FIELD12 IS NULL OR FIELD12 = COALESCE( FIELD4, '0' ) )    AND ( FIELD13 IS NULL OR FIELD13 = COALESCE( FIELD5, '0' ) ) And, if binary shortcutting does not do so already, force the last coalesce per line to be executed only if needed :

   AND case when FIELD12 IS NULL then TRUE else FIELD12 = COALESCE( FIELD4, '0' ) END
   AND case when FIELD13 IS NULL then TRUE else FIELD13 = COALESCE( FIELD5, '0' ) END
( Effects of this step will largely depend on the number of NULLed fields per record in your data; it may even backfire )

Just my 2ct

HansH Received on Tue Aug 21 2007 - 18:08:47 CDT