Re: union all vs. left outer join

From: <>
Date: Wed, 16 Nov 2005 22:16:24 +0000
Message-ID: <dlg38i$4qi$>

David Cressey wrote:

> <> wrote in message
> news:dkqvf8$pmk$

>> 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.
yes I provided the productive test case with some 200'000-400'000 records in each table.


>> 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.
are you interested in the full test case as well ?
> 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.

Henrik Received on Wed Nov 16 2005 - 23:16:24 CET

Original text of this message