Re: Creating 100,000+ tables.

On 2005-12-08, DaLoverhino <DaLoveRhino_at_hotmail.com> wrote:
> 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.)

Neither can I think of any way to speed this up. But... what kind of performance do you want to test? I can't think of any reasonable one.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/