Re: Avoid multiple scan of the same table

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.. I'm now scanning temp table multiple time than the original table.. Aint' I? The table in question is not that wide so oracle may also decide not to materilialize to with query as creating temp table may be more expensive...

Thanks Received on Fri Aug 22 2008 - 16:11:12 CDT