I have a requirement where during the migration new primary keys are assigned ( and old ones are retained in a different column for reference) . Therefore I will have to do a lookup from dependent tables to the master table during the migration of dependent tables to get the new foreign key. This is a big schema and there can be a lot of dependencies like these as well as tables can be huge. As per Oracle documentation, I gathered that I can use SQLEXEC to achieve this. I can use it in two ways (1) By creating a procedure (2) by directly executing a query.
My questions are
(1) What is the impact of using SQLEXEC on performance ? Now this stored procedure or query would get called for each row of dependent table.
(2) Is stored procedure method has some performance benefit over calling query ?
(3) If this is a performance drag, do you have any other idea to achieve the same functionality in goldengate migration ?
(4) Is there anyone who has used this and give me an idea how long some big tables took with this kind of lookup.