0

First, I know there is a common issue in Stack Overflow, but the following solutions are not working well here. So I still need some help.

Oracle - Update COUNT of rows with specific value

Oracle - Update rows with a min value in the group of a column from another table

Oracle update statement with group function

Oracle - Update COUNT of rows with specific value

The problem is: I have a +700k lines table:

REVIEWS (PRODUCT_ID, REVIEW, REVIEW_DATE, RELEASE_DATE, ..., REVIEW_COUNT)

I'm trying to update REVIEW_COUNT by counting the lines with the same PRODUCT_ID (I want just reviews before product release). So the code below works very well for my purpose:

SELECT COUNT(PRODUCT_ID) FROM REVIEWS
WHERE REVIEW_DATE < RELEASE_DATE 
GROUP BY PRODUCT_ID

But I'm having a hard time to do the update. First I tried this:

UPDATE REVIEWS R
SET R.REVIEWS_COUNT = 
(SELECT COUNT(RR.PRODUCT_ID) FROM REVIEWS RR
WHERE RR.DATA < RR.REL_DATE
GROUP BY RR.PRODUCT_ID)

The error is "more than one row", which is not surprising, but since I'm using the group by statement, it shouldn't occur. So I tried a self-join:

UPDATE REVIEWS R
SET R.REVIEWS_COUNT = 
(SELECT COUNT(RR.PRODUCT_ID) FROM REVIEWS RR
WHERE RR.PRODUCT_ID = R.PRODUCT_ID AND RR.DATA < RR.REL_DATE)

But the query is taking forever and I don't think that should take so long, the simple select is pretty normal-fast. I've also tested some more fancy and more simple stuff, but the outcome remains the same: long time waiting and it seems just wrong. Please, what I'm missing in such easy update?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
user13013458
  • 3
  • 2

2 Answers2

0

I think your second update is correct:

UPDATE REVIEWS R
SET R.REVIEWS_COUNT = 
(SELECT COUNT(RR.PRODUCT_ID) FROM REVIEWS RR
WHERE RR.PRODUCT_ID = R.PRODUCT_ID AND RR.DATA < RR.REL_DATE)
;

This will update every record in the reviews table. Is that what you wanted?

An index on product_id will make the inner query run faster, but it will still update all 700K or so records.

Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7
  • Yeah, the REVIEWS_COUNT column is currently empty, so I need update all records. For now, I turned this REVIEWS table into a smaller one, with just the ids (its about 15k lines) and I'm able to work with, since I need to group anyway. But is not expected that I need to work around this way... The query itself is not wrong, right? Why is this taking so long? I mean, just let the update ran for 2 hours and nothing happened. – user13013458 Mar 05 '20 at 16:36
  • @user13013458 the update looked ok to me with my basic understanding of your requirements. Did you check if there is an index on product_id? That would be the best way to speed it up. otherwise it needs to do 700K plus full table scans. There are other things to check as well: 1) blocking lockw 2) if table is very large or fragmented the full scans will be even longer. – Roger Cornejo Mar 05 '20 at 16:40
  • I checked for an index and there was not. I worked on that but seems to take as long as before. Perhaps is my XE Oracle version and my 'great' computer. I'm just a student with very limited resources, may I should consider that as well. Thanks for the hint anyway, the index speeded up other processes. – user13013458 Mar 08 '20 at 13:35
0

Maybe instead of updating you could define view:

select product_id, review_date, release_date, 
       count(case when review_date < release_date then 1 end)
       over (partition by product_id) review_count
  from reviews;

You could also try merge instead update:

merge into reviews a 
using (select product_id, count(product_id) cnt from reviews
        where review_date < release_date 
        group by product_id ) b
on (a.product_id = b.product_id)
when matched then update set reviews_count = b.cnt

dbfiddle

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • . I used views as my work around, but as I said to Roger Cornejo, it seems not right that I can't update my table. Besides the 700k table, the update itself its about only 15k. I think that shouldn't be a problem. I just wanna know why the update is not good for the situation. Anyway, the merge suits beautifully for my needs. Thank you! – user13013458 Mar 08 '20 at 13:42
  • View, or materialized view, is recommended, you don't have to update anything when data changes. But if you want it you can do update or merge. `merge` is more compact, prepares data once and uses this set for update clause. I use it often in similiar situations, even to make syntax shorter. And index on product_id is mandatory when you do such updates or aggregations. – Ponder Stibbons Mar 09 '20 at 10:27