I'm creating a empty table TARGET_TABLE from an existing populated table SOURCE_TABLE using
CREATE TABLE TARGET_TABLE AS SOURCE_TABLE WITH NO DATA
and doing a INSERT/SELECT on these tables.
To my surprise, the last step (MERGE INTO) is taking forever to complete. Despite the fact that there both tables are having exact DDLs, suffiecient database space and while the INSERT/SELECT goes on ,I did a row count using:
LOCKING ROW FOR ACCESS SELECT COUNT (*) FROM TARGET_TABLE;
LOCKING ROW FOR ACCESS SELECT COUNT (*) FROM SOURCE_TABLE;
The count is matching suggesting that inserts is complete but the job still doesnt end. Also when i check the table size , i found that TARGET_TABLE is more in size and is increasing gradually...
From my understanding
Can you check the PI values in your source tables, i guess your source table PI may be highly skewed. If your PI column is highly skewed, then it would impact performance while loading to target even though it has same structures.
Can you try " CREATE TABLE ... WITH DATA" ... this will not take much time as INSERT/SELECT.