CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
CrazyC
CrazyC

Reputation: 1896

Dropping thousands tables make database slow

I am working on some issue where I am creating thousands of tables and doing some testing.

In next iteration i am dropping those tables and again regenerating it. But with these steps, my database becomes very slow.

Even its my local database where i am only working, so its only effect of dropping tables multiple times.

Anyone have any idea to improve the database performance in this scenario. I am dropping these table from PL/SQL developer using UI.

I am using Oracle 11g. Thanks

Upvotes: 1

Views: 590

Answers (1)

Jon Heller
Jon Heller

Reputation: 36902

The main bottleneck is probably the PL/SQL Developer GUI. It can be quite slow when dealing with a large number of objects or windows.

For example, when I created 1000 tables:

begin
    for i in 1 .. 1000 loop
        execute immediate 'create table table'||lpad(i,4,'0')||'(a number)';
    end loop;
end;
/

It took 70 seconds to drop them from the GUI, but only 20 seconds with this script:

begin
    for i in 1 .. 1000 loop
        execute immediate 'drop table table'||lpad(i,4,'0');
    end loop;
end;
/

Upvotes: 1

Related Questions