问Oracle +压缩+临时空间使用

EN

--Create a table and add data.
drop table table1;
create table table1(
    c1 char(100), c2 char(100), c3 char(100), c4 char(100), c5 char(100),
    c6 char(100), c7 char(100), c8 char(100), c9 char(100), c10 char(100)
) compress; -- Remove "compress" and re-run to compare.  Results will not change.
insert /*+ append */ into table1
select level,level,level,level,level,level,level,level,level,level
from dual connect by level <= 100000;
commit;

--There is about 95MB of data.
select 100000*100*10/1024/1024 mb from dual;

--Segment size is 13MB.
select bytes/1024/1024 from dba_segments where segment_name = 'TABLE1';

--Create a new table using a large GROUP BY.
drop table table2;
create table table2 as
select c1,c2,c3,c4,c5,c6,c7,c8,c9,c10
from table1
group by c1,c2,c3,c4,c5,c6,c7,c8,c9,c10;

--In a separate session, measure the temporary tablespace usage.
--The value tops out at 89MB.
select bytes/1024/1024 mb from dba_segments where segment_name = 'TABLE1';