Re: Optimize a query with DISTINCT?

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 Fri Jul 12 2002 - 13:49:36 CEST