MERGE INTO Performance as table grows

Question

This is a general question about the Oracle MERGE INTO statement with a particular scenario, on Oracle RDBMS 12c.

Daily data will be loaded to StagingTableA - about 10m rows. This will be MERGEd INTO TableA. TableA will vary between 0 to 10m rows (matcing StagingTableA). There may be times when TableA will be pruned/emptied and left with 0 rows.

Clearly, when TableA is empty, a straight INSERT will do the job, but the procedure has been written to use a MERGE INTO method to handle all scenarios.

The MERGE .. MATCH is on a indexed column.

My question is an uncertainty about how the MERGE handles the MATCH in circumstances where TableA will start empty, and then grow hugely during the MERGE execution. The MATCH on indexed columns will use a FTS as the stats will show the table has 0 rows.

At some point during the MERGE transaction, this will become inefficient.

Is the MERGE statement clever enough to detect this and change the execution plan, and start using the index instead of the FTS?

If this was done the old way with CURSOR, UPDATE and INSERT then we could potentially introduce a ANALYZE at a appropriate point (say after 50,000 processed) on the TableA to switch to a optimal plan.

I haven't been able to find any documentation dealing with this specific question.

Answer 1

Hopefully you've got a UNIQUE index on that table, which is based on the incoming data. If I was you, rather than using a simple MERGE I'd:

1. Mark all indexes on the table as UNUSABLE, except for the unique index.
2. INSERT all records
3. Catch the DUPLICATE VALUE ON INDEX exception at the time of INSERT and issue the appropriate UPDATE.
4. DELETE processed rows from the input record.
5. Commit every N records (1000? 10000? 100000? Your choice...), calling DBMS_STATS.GATHER_TABLE_STATS for the table you've inserted into after each COMMIT.

Best of luck.