Re: Avoid multiple scan of the same table

From: Dan Blum <>
Date: Thu, 21 Aug 2008 15:41:10 +0000 (UTC)
Message-ID: <g8k2am$t27$> wrote:
> On Aug 21, 9:04?am, wrote:
> > Is there way to avoid multiple scan of the same table in following?
> >
> > Select c1 as v1, c2 as v2, c3 as v3
> > From t1
> > Union
> > Select c1 as v1, c2 as v2, c4 as v3
> > From t1
> > Where ?c5 < 5000
> >
> > The output should look like..
> >
> > v1, v2, v3
> > v11, v21, v31
> > v12, v22, v32
> > and so on.
> >
> > Rows from the both queries can be same so UNION is required to avoid
> > duplicates in current construct.

> It looks like you want to select c4 if c5<5000 else c3.
> If that's the case, then:
> select c1 as v1, c2 as v2, decode(sign(c5-5000),-1,c4,c3)
> From T1;

That does not necessarily produce the same results as the original query:

  1. For rows where c5 < 5000, there will be a result row with c3 and a result row with c4, which will both be kept if the columns have different values.
  2. Duplicate rows within a given half of the original will be removed.

The latter could be fixed with DISTINCT. How to fix the former depends on whether the OP really wants these results (my experience has been that in half these cases or more the original query is not actually correct).

Dan Blum					
"I wouldn't have believed it myself if I hadn't just made it up."
Received on Thu Aug 21 2008 - 10:41:10 CDT

Original text of this message