Rene Nyffenegger wrote:
> 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
>
>
Well, if created in a tablespace with a large initial extend....
I would make the initial extent as small as possible (assuming
it's all about table creation, and you do not have
some crazy plan to actually *use* 100k+ tables)
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Fri Dec 09 2005 - 15:17:45 CST
