I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it
- First I rename the original tables in schema B
- Then, using SQL Developer I copy the table to copy from schema A to B. (I right click the table to copy and select Copy and choose the destination schema)
- Then I create all the indices that exist in the original tables.(Copying tables does not copy indexes, so you have to create them manually)
After the process is over, I run queries against all the tables. Everything goes fine with 9 of them, but with one of the tables the executed query hangs. The next day, when we run the same query against the same table, the execution takes only a moment. I have no idea what the problem is. I tried not adding indexes but it didn't help. I know he way we copy table content might seem weird.
Best Answer
Could it be related to stastics? Remember the same from a data warehouse job.
Table statistics were generated / refreshed not automatically but - once per night. Which can lead to exactly this behavior - first day they suck, then in the night they get refreshed and the query optimizer suddenly makes correct decisions.
Our solution was to manually regenerate statistics during the load processes.
An alternative would be locking behavior.