Re: Avoid multiple scan of the same table
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 22 Aug 2008 10:08:01 -0700
Message-ID: <1219424870.828561@bubbleator.drizzle.com>
>
>
> At some point reading the relevant documentation is a good idea... however:
>
> with x as (select c1, c2, c3, c4, c5 from t1)
> select c1 v1, c2 v2 c3 v3 from x
> union
> select c1 v1, c2 v2, c4 v3 from x where c5 < 5000;
>
> is one way of doing it.
>
> If the table is large this is still not going to perform very well because
> of the work to remove duplicate rows. Given more information about the table,
> a better statement could probably be written.
Date: Fri, 22 Aug 2008 10:08:01 -0700
Message-ID: <1219424870.828561@bubbleator.drizzle.com>
Dan Blum wrote:
> maks71_at_gmail.com wrote:
>> On Aug 21, 7:08?pm, DA Morgan <damor..._at_psoug.org> wrote: >>> 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. >>> One way would be to use a WITH CLAUSE query.http://www.psoug.org/reference/with.html >>> -- >>> Daniel A. Morgan >>> Oracle Ace Director & Instructor >>> University of Washington >>> damor..._at_x.washington.edu (replace x with u to respond) >>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - >>> >>> - Show quoted text -
>
>> Pls. explain with above example.. I have given the problem query with >> expected output..
>
> At some point reading the relevant documentation is a good idea... however:
>
> with x as (select c1, c2, c3, c4, c5 from t1)
> select c1 v1, c2 v2 c3 v3 from x
> union
> select c1 v1, c2 v2, c4 v3 from x where c5 < 5000;
>
> is one way of doing it.
>
> If the table is large this is still not going to perform very well because
> of the work to remove duplicate rows. Given more information about the table,
> a better statement could probably be written.
One way to avoid (not remove) duplicate rows would be to put a WHERE clause on the first statement. Making each set exclusive.
with x as (select c1, c2, c3, c4, c5 from t1)
select c1 v1, c2 v2 c3 v3 from x
where c5 >= 5000;
union
select c1 v1, c2 v2, c4 v3 from x
where c5 < 5000;
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Aug 22 2008 - 12:08:01 CDT