Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Theta Join

sql_coderJul 9 2010 — edited Jul 9 2010
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
This post has been answered by Lakmal Rajapakse on Jul 9 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 6 2010
Added on Jul 9 2010
9 comments
1,800 views