Re: Optimize a query with DISTINCT?

the following example shows how to use WHERE EXISTS as opposed to SELECT DISTINCT and yields a much better explain plan (no sort with the WHERE EXISTS)

       SELECT DISTINCT  d.deptno ,
                        d.dname ,
       FROM             dept d ,
                        emp e 
       WHERE            d.deptno  =  e.deptno ;



       SELECT   d.deptno ,
                d.dname
       FROM     dept d
       WHERE  EXISTS ( SELECT  e.deptno
                       FROM    emp e
                       WHERE   d.deptno = e.deptno ) ;

see if you can integrat ethat logic into your query.

steve

ji_mminm_at_hotmail.com (artxe) wrote in message news:<9309cb1f.0207142327.275ec952_at_posting.google.com>...
> Thank you for your answer jon
>
> I made the changes in the query, but the FULL ACCESS doesn't go with
> the DISTINCT, but without it go...
>
> ¿some more idea?
>
> "Jon Waterhouse" <jonwaterhouse_at_gov.nf.ca> wrote in message news:<3d2ec2e0$1_at_news.mhogaming.com>...
> > I would start by cleaning up your where clause. Your last two clauses select
> > both parts of the same subgroup (one with T.M null, the other with it not
> > null). All clauses have T.A=A.A.
> >
> > I make it
> >
> > TA=AA and (tp is null and aa=pa and pp=dp)
> > or (tp=pp and (td is null and pp=dp)
> > or (td=dd and tp=dp))
> >
> >
> > "artxe" <ji_mminm_at_hotmail.com> wrote in message
> > news:9309cb1f.0207120151.35e10502_at_posting.google.com...
> > > Hi Everybody
> > > I have this query:
> > >
> > > SELECT DISTINCT
> > > territorio.codusuario,
> > > territorio.autonomia,
> > > autonomias.denominacion denccaa,
> > > provincia.provincia,
> > > provincia.denominacion denprov,
> > > delegacion.delegacion,
> > > delegacion.denominacion dendeleg,
> > > territorio.municipio
> > > FROM
> > > delegacion,
> > > provincia,
> > > autonomias,
> > > territorio
> > > WHERE (
> > > ((territorio.autonomia = autonomias.autonomia AND
> > > territorio.provincia IS NULL) AND (autonomias.autonomia =
> > > provincia.autonomia) AND (provincia.provincia =
> > > delegacion.provinciaine))
> > > OR
> > > ((territorio.autonomia = autonomias.autonomia AND
> > > territorio.provincia = provincia.provincia AND territorio.delegacion
> > > IS NULL) AND (provincia.provincia = delegacion.provinciaine))
> > > OR
> > > (territorio.autonomia = autonomias.autonomia AND territorio.provincia
> > > = provincia.provincia AND (territorio.delegacion =
> > > delegacion.delegacion AND territorio.provincia =
> > > delegacion.provinciaine) AND territorio.municipio IS NULL)
> > > OR
> > > (territorio.autonomia = autonomias.autonomia AND territorio.provincia
> > > = provincia.provincia AND (territorio.delegacion =
> > > delegacion.delegacion AND territorio.provincia =
> > > delegacion.provinciaine) AND territorio.municipio IS NOT NULL)
> > > )
> > > and codusuario = 12
> > > ORDER BY DENPROV
> > >
> > > With DISTINCT I have FULL ACCESS in PROVINCIA and DELEGACION AND
> > > TERRITORIO but whitout DISTINCT not...
> > > And with optimizer_mode = RULE is rigth always, with or without
> > > DISTINCT but
> > > I have to put OPTIMIZER_MODE = FIRST_ROW because the client of the
> > > application order me...
> > >
> > > I have Oracle 8
> > >
> > > Excuses for my English, I'm spanish.
> > >
> > > Thanks
> > >
> > > ji_mminm
Received on Mon Jul 15 2002 - 23:07:08 CEST