Hi,
I am loading data from example: "employee" source table to "emp_tgt" (target table). "Employee" source table has 30 million records. It is a straight forward load in pl/sql.
I tried CTAS(Create Table AS) and it completed in 3 mins. But "emp_tgt" is a dimension table. I can not truncate and reload it. I should always do "upsert/merge" operation. I also do not have option of delta load. Always, it has to be full load.
Under these circumstances, "Merge" takes 15 mins. There are no joins involved on source table. I have used PARALLEL hint on select query that gets data from "employee". I also used APPEND and PARALLEL hint on update and insert portion. I disabled constraints/indexes before load and enabled constraints/indexes after load. It still takes 15 mins. I am checking if there is any further tuning I can do on this? Can I say that, this is all we can do and there is no more scope for tuning this load?
I looked at the explain plan, and it uses Full Table scan. I checked methods of tuning full table scans. I found usage of parallel hint, altering db_file_multiblock_read_count parameter. I am not sure if there are any side effects of altering DB_FILE_MULTIBLOCK_READ_COUNT. Moreover I may not have that privilege.
Please suggest any ideas or if I am missing anything.
I appreciate your insights.
Thank you
kumar