Make Sure you have Chosen correct PK as your step may be like this
group by pk_col
having count(1) > 1
which may incurred more time.
Check for Index creation step as well.
If possible then send your code.
Yes, I have chosen the correct Pk, in this step it inserting the error records into the E$ table. It is not inserting any row in the E$ table for the same set of few number of records around 10,000. But for higher number it is hanging...
I checked for Index creation step, it is not available in CKM SQL, it is there in CKM Oracle... Does it make any difference in execution?
post step name and corresponding code for the same for debugging with sample table.
Step name is insert PK errors of CKM SQL
I modified the query with test table name, target table have two columns TEST_ID and APPLICATION_CODE and one primary constraint created on target table as TEST_TABLE_PK in which TEST_ID defined as constraint.
insert into TEST.E$_TEST_TABLE
'ODI-15064: The primary key TEST_TABLE_PK is not unique.',
from TEST.I$_TEST_TABLE TEST_TABLE
where exists (
from TEST.I$_TEST_TABLE E
where TEST_TABLE.TEST_ID = E.TEST_ID
group by E.TEST_ID
having count(*) > 1