Den 2012-07-24 03:14 skrev Leyne, Sean s�h�r:

>
> 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.
>

Good thinking! Thanks! :-) I actually just need the id:s of the master
records, so no real need to join the master at all. Why didn't I think
of that before?

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]