Re: Avoid multiple scan of the same table

From: Jaap W. van Dijk <>
Date: Sun, 24 Aug 2008 18:23:35 GMT
Message-ID: <>

On Thu, 21 Aug 2008 07:04:04 -0700 (PDT), 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
>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.

Like this:

  c1 as v1,
  c2 as v2,
  case when id = 1 then c3 else c4 end as v3 from t1,
(select 1 id from dual union all select 2 id from dual) dupl where = 1 or

     ( = 2 and t1.c5 < 5000 and t1.c3 <> t1.c4)

assuming c3 and c4 are never null, otherwise you have to fiddle with nvl's.

Jaap. Received on Sun Aug 24 2008 - 13:23:35 CDT

Original text of this message