Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: union all vs. left outer join

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@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 - 16:16:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US