Re: Optimize a query with DISTINCT?

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 - 09:27:19 CEST