Re: Optional (ignored) parameters in stored procedures (PL/SQL)? How to?

Theo, do you have experience with this stuff? Is there a chance that this "OR state IS NULL" affects the query execution plan in a harmful way?

Thanks,

Kurta

> OPEN resultCursor FOR
> SELECT UMET_ID
> FROM T_UM_BASE
> WHERE UMBS_MEMB = memberId
> AND (UMBS_OBJ_STT = state
> OR state IS NULL)

"Theo" <theoa_nl_at_hotmail.com> wrote in message news:<4001cb3a$0$127$e4fe514c_at_dreader12.news.xs4all.nl>...
> "Kurta" <submit_at_galleus.com> wrote in message
> news:efcb1994.0401100539.1a9c55e8_at_posting.google.com...
> > I think this gives the same result as the COALESCE example.
> >
> > If the UMBS_OBJ_STT column contains NULL values those rows are not
> > returned. (NULL = NULL returns false) And we should receive all rows
> > when the state parameter is NULL.
> >
> > "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> news:<qomdnesa_eAWQWOi4p2dnA_at_comcast.com>...
> > > OPEN resultCursor FOR
> > > SELECT UMET_ID
> > > FROM T_UM_BASE
> > > WHERE UMBS_MEMB = memberId
> > > AND UMBS_OBJ_STT = nvl(state,UMBS_OBJ_STT)
>
> How about this:
>
> OPEN resultCursor FOR
> SELECT UMET_ID
> FROM T_UM_BASE
> WHERE UMBS_MEMB = memberId
> AND (UMBS_OBJ_STT = state
> OR state IS NULL)
>
> or even this (although I don't like using dummy values):
>
> OPEN resultCursor FOR
> SELECT UMET_ID
> FROM T_UM_BASE
> WHERE UMBS_MEMB = memberId
> AND nvl(UMBS_OBJ_STT,'xxx') = nvl(nvl(state,UMBS_OBJ_STT),'xxx')
>
> But why do you find it necessary to replace the old code? It may give you
> better performance than stuffing it in one cursor.
> Also, using cursor-parameters for memberId and state may perform better. But
> AFAIK you'll have declare your cursor explicitly for that (though I could be
> wrong; I never use inline-cursor like this).
Received on Tue Jan 20 2004 - 05:02:10 CST