Hello,
I'm trying to use a global temporary tables with a bulk insert, and
the performance for the temporary table is about 5 times LESS than
those of a real table - shouldent it be the opposite way?
Here is my example:
- Create real table and a temporary one
create table realTable( id number(15) );
create global temporary table tmpTable(id number(15)) ON COMMIT DELETE
ROWS;
- Insert 50000 records with bulk operation
- into the rea table and the temporary one, and getting the time it
took
declare
tableSize constant number := 50000;
type ty_numTable is varray (50000) of number(15);
numTable ty_numTable := ty_numTable();
st pls_integer;
en pls_integer;
begin
- Fill up a data array
numTable.extend( tableSize );
for i in 1 .. tableSize loop
numTable(i) := i;
end loop;
--
- Inserting data to the real table
st := dbms_utility.get_time();
forall i in numTable.first .. numTable.last
insert into realTable values (numTable(i));
en := dbms_utility.get_Time();
dbms_output.put_line( en-st ); -- Time taken for the insert
--
- Inserting data to the temporary table
st := dbms_utility.get_time();
forall i in numTable.first .. numTable.last
insert into tmpTable values (numTable(i));
en := dbms_utility.get_Time();
dbms_output.put_line( en-st ); -- Time taken for the insert
end;
The output is:
11
57
Do you have any idea?
TIA.
Ronen S.
Received on Tue Jan 27 2004 - 09:57:03 CST
