I have an upcoming production migration I need help with. Table A has hundreds of million of rows. The migration is supposed to update a column in table A, but instead of doing an UPDATE it creates table B (one-to-one with table A) and then does a CTAS to create table C, then drops table B.

The migration developer did not bother to take statistics on table B. The CTAS has a parallel join which is using broadcast instead of hash. It's adding an extra 12 hours to the migration.

Management doesn't want to touch the migration code to add statistics collection or even add SQL hints. I want to tell Oracle to use a parallel hash join (not broadcast) but the sql id for the CTAS does not exist until the migration kicks off. Table B does not exist until the migration kicks off either.

Is it possible to create a sql plan baseline for a query that doesn't exist yet, or to create stats for a table that doesn't exist yet? I know there is an undocument procedure that rewrites incoming SQL code, that could be an option as well.

I know that this is solving the wrong problem, but that's what I need help with.

SQL> exec dbms_sql_translator.create_profile('CTAS');

PL/SQL procedure successfully completed.

SQL> exec dbms_sql_translator.register_sql_translation('CTAS','create table t as select * from dba_objects','create table t as select * from dba_objects where rownum <= 10');

PL/SQL procedure successfully completed.

SQL> alter session set sql_translation_profile = CTAS;

Session altered.

SQL> alter session set events = '10601 trace name context forever, level 32';

Session altered.

SQL> create table t as select * from dba_objects;

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
        10

1 row selected.