Hi,
I needs to update huge partitioned data ( more than 340 millions) and following was the existing query which were using it so far, but it wasn't finishing it now due to huge change and getting SPACE issues as well.
We tried multiple ways like creating temp table for the sub-query and creating indexes on group clauses..etc, and even either(INDEX/full scan) of these scans also helping to finish.
So please suggest me to fix this issue and does any of the following help us.. ?
BULK - forall
CTAS
Query:
UPDATE w_xxxx
SET effective_end_date = to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS') - 1/86400, current_flag = 0
WHERE effective_start_date <> to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS') AND current_flag = 1 AND datasource_id = 1 AND project_object_id IN (
SELECT DISTINCT project_object_id FROM (
SELECT count(*) cnt, project_object_id, resource_object_id, day_wid
FROM w_xxxx
WHERE datasource_id = 1
AND current_flag = 0
GROUP BY project_object_id, resource_object_id, day_wid)
WHERE cnt > 1);