Re: Cost differences - Help!!

On Wed, 02 Jun 2004 01:43:57 -0700, Swedish Viking wrote:

> Hi!
>
> Can anybody give some advice on the differences that I see in my
> environments.
>
> When I run my statement:
>
> select * from a,b where a.id=b.id;
>
> In one of my environents I get the following plan:
> | Operation |Name|Rows|Bytes|Cost
> -------------------------------------------
> | SELECT STATEMENT | |11M|916M|7156
> | HASH JOIN | |11M|916M|7156
> | PARTITION HASH ALL| | | |
> | TABLE ACCESS FULL|a |11M|436M|188
> | PARTITION HASH ALL| | | |
> | TABLE ACCESS FULL|b |11M|510M|3756
>
> In the 2:nd I get:
>
> | Operation |Name|Rows|Bytes|Cost
> -------------------------------------------
> | SELECT STATEMENT | |11M|910M|144266 |
> | HASH JOIN |11M|910M|144266 |
> | PARTITION HASH ALL| | | | |
> | TABLE ACCESS FULL|A|11M|434M| 20081 |
> | PARTITION HASH ALL| | | | |
> | TABLE ACCESS FULL|B|11M|485M| 26435 |
>
> And in the 3:rd I get:
>
> | Operation |Name|Rows|Bytes|Cost
> -------------------------------------------
> | SELECT STATEMENT | |10M|893M|140694
> | PARTITION HASH ALL | | | |
> | HASH JOIN | |10M|893M|140694
> | TABLE ACCESS FULL|A |10M|426M|23913
> | TABLE ACCESS FULL|B |11M|510M|31622
> ------------------------------------------
>
> What causes the differences? Any ideas?
> I have tried to change db_file_mulitblock_read_count without any
> changes in behaviour.
>
> Best Regards / The Swedish Viking

Which version of the database? Plans look exactly the same, the only problem that I have is with the number of rows. You probably ran analyze with estimate option, which gives almost exact results. Also, if it is version 10g, your system statistics is initialized to some insane values, which do influence query cost. As for vikings, you are a welcome change to the Danish variety. There are some really tough raiders from Denmark, gathered around the BARF clan.

-- 
Trust me, I know what I'm doing. (Sledge Hammer)