Re: Avoid multiple scan of the same table

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.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."