Re: Global Temporary Table

"Laly" <lalydba_at_free.fr> wrote in message news:6f8684ef.0409072331.21188c4d_at_posting.google.com...
| Now we recreated our temp tablespace as a DMT with tempfiles.
|
| Here is my table :
| create global temporary table test (
| x number,
| y number,
| z char(100)
| )
| on commit preserve rows;
|
| And my procedure :
|
| create or replace procedure p_test as
| l_start number;
| l_end number;
| begin
| l_start := dbms_utility.get_time;
| for i in 1..10000
| loop
| if i mod 100 = 0 then
| l_end := dbms_utility.get_time;
| dbms_output.put_line(i || '=>' || round((l_end-l_start)/100) || '
sec');
| l_start := l_end;
| end if;
|
| delete test;
|
| for j in 1..20
| loop
| insert into test(x)
| values (j);
| end loop;
|
| update test
| set y = x,
| z = '1';
|
| end loop;
| end;
|
| The output is :
| 100=>2 sec
| ...
| 1000=>3 sec
| ...
| 1400=>4 sec
| ...
| 1800=>5 sec
| ...
| 2600=>6 sec
| ...
| 3100=>7 sec
| ...
| 3800=>8 sec
| ...
| 4000=>9 sec
| ...
| 4800=>10 sec
| ...
| 5500=>11 sec
| ...
| 9900=>17 sec
| 10000=>18 sec
|
| My thought is that if space was reused, the GTT would stay in a reasonable
size
| instead of spending a lot of time doing the full table scan.
|
| I have to say that if we commit inside the loop every 500 step, we don't
have
| this performance issue.
|
| Can somebody reproduce this problem and explain what is happening here,
please ?
|
|
| Best regards,
|
| Laly.

your update statement will always do a full table scan because you are updating all rows in the table

++ mcs Received on Wed Sep 08 2004 - 05:51:48 CDT