Oracle Database Discussions

Hi,

We are running 10.2.0.3.0 on Aix 5.3. We are seeing massive performance degredation with statements resolved by hash unique.

A statement of the type

update tab set col =
(select distinct col
from othertab
where othertab.joincol = tab.joincol)

will be resolved using a hash unique at the distinct point and will run very, very slowly.

if we change the statement to two statements...

insert into temptab
(select distinct joincol, col
from othertab);

update tab
set col = (select col from temptab where temptab.joincol = tab.joincol)

Then performance will be exponentially quicker even though the insert will still do a hash unique.

We have opened an SR, we have tried coding round the hash unique using group by, we have checked the hashing is not being done on disk, we have checked for swapping, we have set the work_area_size policy to manual and provided 1GB of physical memory for the session - only a fraction got used and the statement ran slowly.

Any one any suggestions?