Re: Avoid multiple scan of the same table
Date: Thu, 21 Aug 2008 07:58:18 -0700 (PDT)
Message-ID: <7c6e6ce2-c068-4bdb-a70c-f882aaab4e43@z66g2000hsc.googlegroups.com>
On Aug 21, 10:04 am, mak..._at_gmail.com 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.
I do not think that you constructed your example correctly since you are selecting the exact same columns from the same table but you apply a where clause only to the second query but all the rows fetched by the second query where fetched by the first query yet you are going to discard the duplicates.
Please post the create DDL and insert statements for sample data then explain what you need. Then maybe someone can help you.
HTH -- Mark D Powell -- Received on Thu Aug 21 2008 - 09:58:18 CDT