Re: union all vs. left outer join
From: <henrik.sorensen_at_balcab.ch>
Date: Wed, 16 Nov 2005 22:16:24 +0000
Message-ID: <dlg38i$4qi$1_at_news.hispeed.ch>
> 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.
> Does it still do the scan on a production sized database?
yes
>
> 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.
Date: Wed, 16 Nov 2005 22:16:24 +0000
Message-ID: <dlg38i$4qi$1_at_news.hispeed.ch>
David Cressey wrote:
>
> <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.
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?
yes
>
> 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