SQL & PL/SQL

hello oracle community,

DMBS 11.1g:

I have a performance problem with an query/update. I have two tables, in one table (lets call it BonusTipp table) I have about 5000 records with unique codes in a column. Another table has a column (table CompanyShare with over 1 million records) that is filled for most records, but for about 3500 it is NULL. Now I want to fill those 3500 emtpy records in the CompanyShare table with values out of the bonuscode table. So every NULL should get a unique code. I have the update query for this, but it is a bit slow.


UPDATE mms.CompanyShare cs SET cs.BonusTipp = (SELECT AK_BonusTipp
FROM mms.BonusTipp bt2
WHERE (SELECT COUNT(*)
FROM mms.BonusTipp bt3
WHERE bt3.PK_BonusTipp <= bt2.PK_BonusTipp
)
=
(SELECT COUNT(*)
FROM mms.CompanyShare cs3
WHERE cs3.BonusTipp IS NULL
AND cs3.PK_CompanyShare <= cs.PK_CompanyShare
)
)
WHERE cs.BonusTipp IS NULL
;

my question is, is there another way to avoid the theta joins over the primary keys?

Ikrischer

Edited by: Ikrischer on Jul 9, 2010 10:26 AM

Edited by: Ikrischer on Jul 9, 2010 10:31 AM