Home » SQL & PL/SQL » SQL & PL/SQL » compress table
compress table [message #230223] Wed, 11 April 2007 04:25 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello,

I have a process where I insert data into a table. The process lasts several days, the table gets therefore quite big (40 GB). When I create a copy of this table (create table as ...), then the table is just about 8 GB. The issue is that my tablespace is at the limit. How can I compress the table during the process, so that the table will never get that big? Is there a command like 'compress' or so?

Thanks
Stefan
Re: compress table [message #230234 is a reply to message #230223] Wed, 11 April 2007 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Alter table ... move
Alter table ... shrink space compact

Regards
Michel

Re: compress table [message #230528 is a reply to message #230234] Thu, 12 April 2007 03:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is something about your process that is leaving whitespace behind. Is it updating or deleting rows as well as inserting? Is it performing direct path inserts and then deleting some of the rows?

Ross Leishman
Re: compress table [message #230964 is a reply to message #230528] Fri, 13 April 2007 10:31 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
My process is only inserting records into the table P10.

After inserting, the table is 35 GB.

Then I process the code

alter table P10 enable row movement;
alter table P10 shrink space cascade;


... and the table is 26 GB after this.

Then I process the code

create table test as
select * from P10

... and the table is just 12 GB.

Obviously, the shrink-code does not compact the table very much. How can I compact the table without creating a new one?

Thanks Stefan
Re: compress table [message #230966 is a reply to message #230964] Fri, 13 April 2007 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Alter table ... move

Regards
Michel
Re: compress table [message #230975 is a reply to message #230223] Fri, 13 April 2007 10:48 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
I tried the move-code, but I get "ORA-01652: unable to extend temp segment by 8192 in tablespace". So I don't have enought space for that. Is there another way ?

Thanks
Stefan
Re: compress table [message #231021 is a reply to message #230223] Fri, 13 April 2007 13:08 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
First, that space error just means you don't have enough room in your tablespace to hold two copies of the same table. One copy being the original, the other being the new one created when you move it and before the original gets removed.

Second, you can use move to "rebuild" the table. There are two different concepts here though. One is the idea of space inside a table, fragmentation if you will, and the other is oracle table compression. You can also use alter table move with the compress keyword. Oracle does have compression, and you can look into the docs for more info (on the alter table move statement, as well as the docs on create table.)

Third, in 10g, data will only be compressed in certain situations, such as direct path inserts, and moves, and create table as select (which are all probably considered direct path inserts). So if your table (or tablespace) is created with the compress option, the create table as select operation will itself compress the data, gaining further (and different) space savings than the simple "rebuilding" of the table would yield.

I hope all of that wasn't too confusing, it could be phrased better, but definitely check into docs for more details. Also consider the following demo. Assume the CTAS is the unknown.

A regular insert (my database is in logging mode) will not be in direct path mode, and will therefore not compress data. But a direct path insert (the one with the append hint) will be in direct path. So those are the two knowns for comparison to the CTAS table.

You could further modify this example to have various row level deletions and updates and inserts to introduce whitespace, and then see the space savings from the rebuild itself (both with and without compression).

MYDBA@orcl > 
MYDBA@orcl > create table test1 as select * from all_objects;

Table created.

MYDBA@orcl > 
MYDBA@orcl > create table test2 as select * from all_objects where 1=0;

Table created.

MYDBA@orcl > insert /*+ append */ into test2 select * from all_objects;

51695 rows created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > 
MYDBA@orcl > create table test3 as select * from all_objects where 1=0;

Table created.

MYDBA@orcl > insert into test3 select * from all_objects;

51696 rows created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > 
MYDBA@orcl > select segment_name, count(*) from dba_extents
  2  where owner = 'MYDBA' and segment_name like 'TEST%'
  3  group by segment_name order by segment_name;

SEGMENT_NAME                     COUNT(*)
------------------------------ ----------
TEST1                                  21
TEST2                                  21
TEST3                                  21

3 rows selected.

MYDBA@orcl > 
MYDBA@orcl > drop table test1;

Table dropped.

MYDBA@orcl > drop table test2;

Table dropped.

MYDBA@orcl > drop table test3;

Table dropped.

MYDBA@orcl > 
MYDBA@orcl > create table test1 compress as select * from all_objects;

Table created.

MYDBA@orcl > 
MYDBA@orcl > create table test2 compress as select * from all_objects where 1=0;

Table created.

MYDBA@orcl > insert /*+ append */ into test2 select * from all_objects;

51698 rows created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > 
MYDBA@orcl > create table test3 compress as select * from all_objects where 1=0;

Table created.

MYDBA@orcl > insert into test3 select * from all_objects;

51699 rows created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl > 
MYDBA@orcl > select segment_name, count(*) from dba_extents
  2  where owner = 'MYDBA' and segment_name like 'TEST%'
  3  group by segment_name order by segment_name;

SEGMENT_NAME                     COUNT(*)
------------------------------ ----------
TEST1                                  17
TEST2                                  17
TEST3                                  21

3 rows selected.

MYDBA@orcl > 
MYDBA@orcl > drop table test1;

Table dropped.

MYDBA@orcl > drop table test2;

Table dropped.

MYDBA@orcl > drop table test3;

Table dropped.

MYDBA@orcl > 
MYDBA@orcl > spool off


Re: compress table [message #231090 is a reply to message #231021] Sat, 14 April 2007 01:39 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, there was a lot of information in Scot's last post, but it comes down to one of three possibilities:

1. The loaded table contains a lot of whitespace.
2. The rebuilt table is compressed (as Scot said - most likely)
3. You have made a mistake, and the rebuilt version is not actually bigger.

So lets find out:

1. You have already told us you are not deleting or updating in any way, this is the most likely reason for the difference. Another possibility is the PCTFREE value:
SELECT table_name, pct_free
FROM   user_tables
WHERE  table_name IN ('TEST','P10')
Are they different?

If so, the PCTFREE for P10 is too big or the default for its tablespace is too big. Use a smaller value, say 5 or 10.

2.
SELECT compression FROM user_tables WHERE table_name = 'TEST'
Is it enabled? The CREATE TABLE AS SELECT will compress, but if P10 is loaded with conventional INSERTS, it will not be compressed.

To make it compressed as it loads, you must perform DIRECT PATH inserts with SQL*Loader or INSERT /*+APPEND*/.

3. What are you using to determine the size?
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN ('TEST','P10')


Ross Leishman
Previous Topic: How to create word document
Next Topic: SUM function..??
Goto Forum:
  


Current Time: Wed Dec 07 10:33:48 CST 2016

Total time taken to generate the page: 0.15251 seconds