Den 2012-07-23 00:12 skrev Helen Borrie s�h�r:

>
>
> >Den 2012-07-22 22:35 skrev Ricardo Uzcategui s�h�r:
> >> Maybe if you do a join...
> >>
> >> like this...
> >>
> >> select m.* from master m
> >> join detail d
> >> on (m.id = d.master)
> >> where (d.removed is not null)
> >
> At 09:51 AM 23/07/2012, Kjell Rilbe wrote:
>
> >Thanks for trying, but that would return all master records where there
> >is at least one detail that's not removed. Instead, I need all the
> >masters where there are removed details, but no current ("unremoved")
> ones.
>
> select m.* from master m
> join detail d
> on (m.id = d.master)
> where d.removed is null
> and (not exists
> (select 1 from detail d2
> where d2.master = m.id
> and d2.removed is not null))
>

Nice idea, but I think that would return masters with at least one
current detail and no removed ones. Correcting that, I get this SQL:

select m.* from master m
join detail d
on (m.id = d.master)
where d.removed is not null
and not exists
(select 1 from detail d2
where d2.master = m.id
and d2.removed is null)

But this query would join like 99.9999 percent of the masters with
details, and for each one execute the subquery, i.e. about 200 million
subquery executions.

I tried this:
select M."Id"
from "Master" M
inner join "Detail" D on D."Master" = M."Id"
group by M."Id"
having count(D."Id") > 0
and count(case when D."Removed" is null then 1 end) = 0

It returned 7 records after almost 9 hours using a plan like this:
PLAN JOIN (M ORDER IX_PK_Master, D INDEX (IX_Detail_Master))
So, it did use relevant indices, but still had to join all 100+200
million records.

> I think you said that detail.master is not a declared FK. Do you have
> ascending and descending indexes on it, though?
>

No, only ascending. Are you thinking that the "is null" condition would
only benefit from a descending index?

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



[Non-text portions of this message have been removed]