Creating 100,000+ tables.

Hello, for performance testing, I need to create over 100,000 tables in oracle.
One problem is, creating that many tables takes a very long time, and I'm wondering if there was a way I can speed up the creation?

here's the script that I have written:

CREATE OR REPLACE PROCEDURE create_ttables( schema varchar2, fromX INTEGER, toY INTEGER)
AS
  ncount INTEGER;
  ncreated INTEGER;
  basename VARCHAR2(20);
  tablename VARCHAR2(60);
  create_table_string VARCHAR2(1000);
  drop_table_string VARCHAR2(1000);
BEGIN

  basename := 'ttable_';
  ncount   := fromX;
  ncreated := 0;

  if fromX > toY
  then
    raise_application_error( -20000, 'fromX > toY');   end if;

  FOR ncount IN fromX .. toY
  LOOP
     tablename := schema || '.' || basename || ncount;

     drop_table_string := 'DROP TABLE ' || tablename;

     BEGIN
       EXECUTE IMMEDIATE drop_table_string;
     EXCEPTION
       WHEN others then null;
     END;

     create_table_string := 'CREATE TABLE ' || tablename ||
                            '( id integer, name varchar2(30))';

     ncreated := ncreated + 1;

     EXECUTE IMMEDIATE create_table_string;

     commit;

     --Print progress report, since creation may take a long time.


--     if mod(ncreated, 100) = 0

--     then

--       DBMS_OUTPUT.PUT_LINE( 'Created ' || ncreated || 'tables.');

--     end if;

  END LOOP;
END;
/

I can't think of any way to speed this up. Perhaps there is a secret DBA tool that I can use to make it much faster? (I like to 'overkill' in performance testing, so I'm looking to make a half a million tables.)

Thanks. Received on Wed Dec 07 2005 - 18:14:31 CST