the other thin is creating a trigger that update master field (a new field)
to update this value, and, you will index that and not need to join or
subselect to detail table..

i do a lot of time to solve problem like this!

On Sun, Jul 22, 2012 at 5:54 PM, Leyne, Sean <Sean@...>wrote:

> **
>
>
> 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?
>
> What is the "selectivity" of Detail rows to Master (What % of Master don't
> have Details)?
>
> I am afraid that there won't be much that can be done to avoid the basic
> problem.
>
> Sean
>
>
>



--
Ricardo...


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