Re: Multiple indexes, huge table

On Thu, Sep 6, 2012 at 5:12 PM, Alan Hodgson <ahodgson(at)simkin(dot)ca> wrote:
> On Thursday, September 06, 2012 05:06:27 PM Jeff Janes wrote:
>> For updating 20 million out of 500 million rows, wouldn't a full table
>> scan generally be preferable to an index scan anyway?
>>
>
> Not one table scan for each row updated ...

My understanding is that he was only trying to update one row on the
parent table anyway, which then cascaded to 20,000,000 rows on the
child/fact table.

If you mean one table scan for each of the 20,000,000 rows *of the
child* being updated, that isn't what it does now, index or not.

Even if he were updating 10 rows of the parent table, I think it would
still be the case that if one sequential scan of the child/fact was
faster than one (large, low-cardinality, unclustered) index scan, then
10 sequential scans would be faster than 10 index scans.

Cheers,

Jeff