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
The most optimized INSERT SELECT occurs when a target table:
• Is empty AND
• Has the same PI as the source table(s)
Despite matching this best case scenerio Why the fastest method of moving data (INSERT/SELECT) is taking this much of time and what is going on despite the insert showing same row counts. Why the job is not ending and why the table space is increasing ?
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.
Check to see if you have any secondary indexes. If so, might want to drop and then re-create.