Reputation: 11971
I have a royalties frame and a batches frame. Batches consist of a bunch of royalties batched together.
Basically when in the batch frame, it updates the unallocated royalties after an allocation has been done. However, it tries to match by ROYALTY_ID, which takes forever because there are millions of them. I want to restrict the function by BATCH_ID so it'll massively reduce the search time, but I'm not quite sure how.
Thanks Dan
Current query:
UPDATE ROYALTIES
SET ALLOCATION_STATUS_ID = pRoyaltyStatusId
WHERE ROYALTIES.RIGHT_TYPE_ID = pRightTypeId
AND ROYALTIES.WORK_ID = pWorkId
AND ROYALTIES.TERRITORY_ID = pTerritoryId
AND ROYALTIES.ROYALTY_PERIOD_END_DATE = UTILS.FIX_DATE(pEndDate);
Upvotes: 0
Views: 101
Reputation: 10517
No, foreign key constraint does not connect with performance issue. You should look at the INDEX technique. The index for the field BATCH_ID (details table) will improve select query performance.
Upvotes: 1