Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

  1. First I rename the original tables in schema B
  2. 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)
  3. 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.

share|improve this question
I bet you forgot to type "commit" after doing your activities – Phil Feb 10 '14 at 8:56
DDL doe snot need commit, it's done implicitly. Furthermore, I don't do it manually, I use table copy wizard. – Mikayil Abdullayev Feb 10 '14 at 11:23
up vote 2 down vote accepted

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.

share|improve this answer

Not sure if the query against that one table really "hangs", it might just be taking much longer time to execute. If you say things look fine next day, the lack of statistics on the newly created tables might be the reason for poor performance. Next time, you follow your process of "copying" tables, you may want to explicitly collect statistics on newly created tables and their indexes before you run the queries.

share|improve this answer
Why I say it really hangs is because the next day the query runs in lest than just a second. – Mikayil Abdullayev Feb 10 '14 at 8:45
Is this one table big compared to others? – Jayadevan Feb 10 '14 at 9:09
Yes, it is. user34040 was right about statistics. Gathering statistics solved the problem – Mikayil Abdullayev Feb 10 '14 at 10:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.