INSERT SELECT Performance mystery

Database
Enthusiast

INSERT SELECT Performance mystery

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 ?
2 REPLIES
Enthusiast

Re: INSERT SELECT Performance mystery

Hi,

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.

Enthusiast

Re: INSERT SELECT Performance mystery

Check to see if you have any secondary indexes.  If so, might want to drop and then re-create.