This is a procedure I wrote in order to help me to create a huge table for testing quickly( The idea of the code is borrowed from Tom Kyte’s book). This stored proc takes two parameters, table_name for the name of the table to be created and table_size for the number of rows to be loaded to the newly created table . Some of the advantages of this procedure are that this stored proc create table with nologging and load the data to the table with APPEND hint.
Therefore this stored proc can create a huge table quickly because of minminal redo log generation.
CREATE OR REPLACE PROCEDURE Build_Table
(Table_Name IN VARCHAR,
Table_Size NUMBER)
IS
sql_stmt VARCHAR(2000);
l_cnt NUMBER := 0;
l_Rows NUMBER := Table_Size;
BEGIN
sql_stmt := ‘create table ‘ –create table structure
||Table_Name
||’ nologging
as select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0′;
EXECUTE IMMEDIATE sql_stmt;
COMMIT;
sql_stmt := ‘insert /*+ APPEND */ into ‘ –load the data
||Table_Name
||’ select rownum,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects
where rownum<=:1′;
EXECUTE IMMEDIATE sql_stmt
USING l_Rows;
l_cnt := l_cnt + SQL%ROWCOUNT;
COMMIT;
WHILE (l_cnt < l_Rows) LOOP
sql_stmt := ‘insert /*+ APPEND */ into ‘
||Table_Name
||’ select rownum+:1,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from ‘
||Table_Name
||’ where rownum <= :2-:3′;
EXECUTE IMMEDIATE sql_stmt
USING l_cnt,l_Rows,l_cnt;
l_cnt := l_cnt + SQL%ROWCOUNT;
COMMIT;
END LOOP;
sql_stmt := ‘alter table ‘ –create the primary after loading the data
||Table_Name
||’ add constraint ‘
||Table_Name
||’_pk primary key(id)’;
EXECUTE IMMEDIATE sql_stmt;
sql_stmt := ‘ BEGIN dbms_stats.gather_table_stats –update the table statistics
( ownname => SYS_CONTEXT (”USERENV”, ”CURRENT_USER”),
tabname =>”’
||Upper(Table_Name)
||”’,
cascade => TRUE ); end;’;
EXECUTE IMMEDIATE sql_stmt;
END;
CREATE OR REPLACE PROCEDURE Build_Table
(Table_Name IN VARCHAR,
Table_Size NUMBER)
IS
sql_stmt VARCHAR(2000);
l_cnt NUMBER := 0;
l_Rows NUMBER := Table_Size;
BEGIN
sql_stmt := ‘create table ‘ –create table structure
||Table_Name
||’ nologging
as select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0′;
EXECUTE IMMEDIATE sql_stmt;
COMMIT;
sql_stmt := ‘insert /*+ APPEND */ into ‘ –load the data
||Table_Name
||’ select rownum,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects
where rownum<=:1′;
EXECUTE IMMEDIATE sql_stmt
USING l_Rows;
l_cnt := l_cnt + SQL%ROWCOUNT;
COMMIT;
WHILE (l_cnt < l_Rows) LOOP
sql_stmt := ‘insert /*+ APPEND */ into ‘
||Table_Name
||’ select rownum+:1,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from ‘
||Table_Name
||’ where rownum <= :2-:3′;
EXECUTE IMMEDIATE sql_stmt
USING l_cnt,l_Rows,l_cnt;
l_cnt := l_cnt + SQL%ROWCOUNT;
COMMIT;
END LOOP;
sql_stmt := ‘alter table ‘ –create the primary after loading the data
||Table_Name
||’ add constraint ‘
||Table_Name
||’_pk primary key(id)’;
EXECUTE IMMEDIATE sql_stmt;
sql_stmt := ‘ BEGIN dbms_stats.gather_table_stats –update the table statistics
( ownname => ”SYSTEM”,
tabname =>”’
||Upper(Table_Name)
||”’,
cascade => TRUE ); end;’;
EXECUTE IMMEDIATE sql_stmt;
END;
References
http://www.dbspecialists.com/files/presentations/load_faster.html
