Kjell,

> > Trying to figure out a way to do a search/query with decent performance.
> >
> > Table "Master" contains roughly 100 million records and table "Detail"
> > contains roughly 200 million records.
>
> > Unfortunately, this will cause a natural scan of the 100 million
> > record "Master" table, and roughly 200 million subselects...
> >
> > Can anyone figure out a way to avoid a full table scan?

The best solution that I have come up with is:

SELECT M.*
FROM (
SELECT D."Master" as M_ID, SUM( CASE WHEN (D."Removed" IS NULL) THEN 1 ELSE 0 END) as NotCurrentCount
FROM "Detail" D
GROUP BY 1
) T
JOIN "Master" M ON M."ID" = T.M_ID
WHERE
T.NotCurrentCount = 0

This does require a natural scan of the Detail table*, but it will avoid the 200 million sub-selects; which I suspect will mean a much faster performance.


Sean

* you will want to force a natural scan by changing:
D."Master" as M_ID
to read:
D."Master"+0 as M_ID
if necessary