Re: Avoid multiple scan of the same table
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 22 Aug 2008 21:17:30 -0700
Message-ID: <1219465040.328527@bubbleator.drizzle.com>
>> DA Morgan <damor..._at_psoug.org> wrote:
>> Except that if c3 != c4 then we always want the c3 row, regardless of the value of
>> c5. A better option would be
>> 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
>> and c3 != c4;
>> That ensures that nothing in the bottom set matches anything in the top set,
>> although of course each set could have duplicates within it as far as we know.
>> If anything there (c1 or c2 or the combination) is a unique key then in this
>> version we can replace UNION with UNION ALL. If not, it might still be
>> faster to use UNION ALL and put a DISTINCT (or GROUP BY) on each query - unless
>> one set is much larger than the other, it will almost certainly be faster.
>> --
>> _______________________________________________________________________
>> Dan Blum t..._at_panix.com
>> "I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -
>> - Show quoted text -
Date: Fri, 22 Aug 2008 21:17:30 -0700
Message-ID: <1219465040.328527@bubbleator.drizzle.com>
maks71_at_gmail.com wrote:
> On Aug 22, 2:08 pm, t..._at_panix.com (Dan Blum) wrote:
>> DA Morgan <damor..._at_psoug.org> wrote:
>>> Dan Blum wrote: >>>> mak..._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;
>> Except that if c3 != c4 then we always want the c3 row, regardless of the value of
>> c5. A better option would be
>>
>> 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
>> and c3 != c4;
>>
>> That ensures that nothing in the bottom set matches anything in the top set,
>> although of course each set could have duplicates within it as far as we know.
>> If anything there (c1 or c2 or the combination) is a unique key then in this
>> version we can replace UNION with UNION ALL. If not, it might still be
>> faster to use UNION ALL and put a DISTINCT (or GROUP BY) on each query - unless
>> one set is much larger than the other, it will almost certainly be faster.
>>
>> --
>> _______________________________________________________________________
>> Dan Blum t..._at_panix.com
>> "I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -
>>
>> - Show quoted text -
> > Folks, > > I'm still not avoiding multiple scan of the table.. With "With > Clause" , I believe Oracle creates the temp table and then use that > table later in the query..
Oracle creates the temp table in memory. It may need to swap the temp table to disk and then again it may not. Try it and examine an Explain Plan output created using DBMS_XPLAN.
This is not significantly different from the use of in-line views.
-- 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 - 23:17:30 CDT