Global temporary tables performance

Giganews Newsgroups
Subject: Global temporary tables performance
Posted by:  ronen…@hotmail.com
Date: 27 Jan 2004

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.

Replies