Re: pl/sql conditional cursor?
From: steph <stephan0h_at_yahoo.de>
Date: Thu, 4 Jun 2009 07:23:59 -0700 (PDT)
Message-ID: <6aaf16d4-6cc1-4ad4-9216-5d1de10c2d16_at_g20g2000vba.googlegroups.com>
On 4 Jun., 12:17, Peter Kallweit <p_kallw..._at_arcor.de> wrote:
> steph wrote:
> > On 4 Jun., 11:05, Peter Kallweit <p_kallw..._at_arcor.de> wrote:
> >> steph wrote:
> >>> hi,
> >>> Oracle 10g. In a procedure I have some code like
> >>> declare
> >>> cursor c1 is
> >>> select x,y
> >>> from tab1,tab2
> >>> where <condition1>
> >>> and <condition2>
> >>> and <condition3>;
> >>> begin
> >>> for r1 in c1 loop
> >>> <some code>
> >>> end loop;
> >>> end;
> >>> Now I want to turn condition 3 on/off depending on one of the
> >>> procedure's parameters - without having to define a second cursor
> >>> (because this would mean duplicating some amount of code). So what I
> >>> don't want to do is this:
> >> Hi Stephan,
> >> how about
> >> and (<parametersaysoff> or <condition3>);
> >> hth
> >> Peter
> > having 2 distinct cursors?
> The execution plans will be different - sure.
> Regarding the performance, I'd think it depends on the kind and
> complexity of the conditions.
> The only way to find out, if the performance remains acceptable, is to
> give it a try ;-)
Date: Thu, 4 Jun 2009 07:23:59 -0700 (PDT)
Message-ID: <6aaf16d4-6cc1-4ad4-9216-5d1de10c2d16_at_g20g2000vba.googlegroups.com>
On 4 Jun., 12:17, Peter Kallweit <p_kallw..._at_arcor.de> wrote:
> steph wrote:
> > On 4 Jun., 11:05, Peter Kallweit <p_kallw..._at_arcor.de> wrote:
> >> steph wrote:
> >>> hi,
> >>> Oracle 10g. In a procedure I have some code like
> >>> declare
> >>> cursor c1 is
> >>> select x,y
> >>> from tab1,tab2
> >>> where <condition1>
> >>> and <condition2>
> >>> and <condition3>;
> >>> begin
> >>> for r1 in c1 loop
> >>> <some code>
> >>> end loop;
> >>> end;
> >>> Now I want to turn condition 3 on/off depending on one of the
> >>> procedure's parameters - without having to define a second cursor
> >>> (because this would mean duplicating some amount of code). So what I
> >>> don't want to do is this:
> >> Hi Stephan,
>
> >> how about
>> >> and <condition2>
> >> cursor c1 is
> >> select x,y
> >> from tab1,tab2
> >> where <condition1>
> >> and (<parametersaysoff> or <condition3>);
>
> >> hth
> >> Peter
>> > query? At least the execution plan is surely different then when
> > I'm not sure if this wouldn't possibly degrade performance of my
> > having 2 distinct cursors?
>
> The execution plans will be different - sure.
> Regarding the performance, I'd think it depends on the kind and
> complexity of the conditions.
> The only way to find out, if the performance remains acceptable, is to
> give it a try ;-)
gave it a try and i guess the complexity of my query is sufficiently high as to degrade performance with this solution. so i'll go down the duplicate-cursor path ... Received on Thu Jun 04 2009 - 09:23:59 CDT