CREATE TABLE "TMP02"
( "ID" VARCHAR2(5 BYTE),
"MID" VARCHAR2(5 BYTE),
"GROUP" NUMBER
)
insert into tmp02 values (1,2,'');
insert into tmp02 values (3,4,'');
insert into tmp02 values (5,6,'');
insert into tmp02 values (5,2,'');
Merge statement is not getting crashed on 600 k rows.it is working fine for 100-200 rows.it is populating same grp no for a connected row.connected row =>
id = PRIOR id
OR mid = PRIOR mid
or id = prior mid
or mid = prior id
MERGE INTO tmp02 dst
USING (
SELECT id, mid
, DENSE_RANK () OVER (ORDER BY MIN (CONNECT_BY_ROOT id)) AS grp
FROM tmp02
GROUP BY id, mid
CONNECT BY NOCYCLE id = PRIOR id
OR mid = PRIOR mid
or id = prior mid
or mid = prior id
) src
ON ( src.id = dst.id
AND src.mid = dst.mid
)
WHEN MATCHED THEN UPDATE
SET dst.grp = src.grp
;
version is 11.2.0.4.0
could you please let me know, how to improve the performance or any alternative way?