Re: union all vs. left outer join

<henrik.sorensen_at_balcab.ch> wrote in message news:dkqvf8$pmk$2_at_news.hispeed.ch...

> After all it doesn't make sense to scan the whole table, when not
necessary.

Yes, it does, under certain circumstances. If the entire table can be scanned out of a single disk block,
but access via index requires two disk blocks in memory, then it's a waste of memory, or of disk IO transfers,
to copy the index into memory.

Sure the scan will burn a little more CPU time, but the difference will be trivial, for tables that fit in a single block.

That is probably why VC said you should come up with a production sized test case.

>
> I have verified this on a large production sized database with the real
> data, and the full scan of A-B-C cannot be right.
>
>
Does it still do the scan on a production sized database?

I'm more familiar with the optinmizer in Oracle Rdb (formerly DEC Rdb/VMS) than with classic Oracle's optimizer or any other product's optimizer.

Oracle Rdb's optimizer estimates the cost based on (virtual) disk accesses, not CPU time. That's usually the bottleneck, when speed matters.

The cost of a strategy will vary nonlinearly with the size of tables. Thus one strategy can be chosen for small tables and a different one for large tables.

But then, it's always possible that the strategy generator isn't generating the right strategy, or the strategy evaluator is evaluating multiple strategies wrongly. This varies from product to product, and from product version to product version. Received on Wed Nov 16 2005 - 10:08:11 CET