This website is not affiliated with, sponsored by, or approved by SAP AG.

reorg of compressed table

Basis (Basis Technology Modules: Basis Component/System Administration, GUIs)

Moderators: Snowy, thx4allthefish

reorg of compressed table

Postby Zavaros » Sat Feb 02, 2013 7:43 pm

Hello,

I had an interesting question and right now I can not respond it. Can I ask for your help?

so:
As I remember the oracle compresses the segments in the background (in asynchron mode). This means that when I push lot of data into a table then:
1. the segments are compressed when table import is finished. It will cause that lot of free space will apear in segments after reorg -- table can be bigger than before reorg
2. the segments are compressed during import. Free space "bubles" pop-up in older segments what will cause that records will not be stored in sequence. It could neagtively affect the speed of full-table scans.

Am I right?
Does it means that compressed tables should not be reorganized ? ... or only very slowly :lol:

Regards,

Zav
Zavaros
 
Posts: 756
Joined: Thu Oct 24, 2002 10:50 pm
Location: Hungary

Re: reorg of compressed table

Postby blueteeth » Sun Feb 03, 2013 12:45 am

Perhaps this may help you:

http://www.oracle-ckpt.com/advanced-table-compression-with-oracle-database-11g-for-oltp/

The example is SAP specific.

I'd agree with you that compressed tables should rarely be reorganized. If the tables are becoming fragmented due to frequent DML, perhaps they should have not be compressed in the first place.
blueteeth
 
Posts: 499
Joined: Sat Apr 05, 2008 12:22 pm

Re: reorg of compressed table

Postby Zavaros » Sun Feb 03, 2013 5:08 pm

hello,

thank you Blueteeth. There are lot of similar documents on the Net... They all speak about advantages and degrades the disadvantages.

I do not question that the "compress for OLTP" shrinks the table. It was tested and proved.

Regards,

Zav.

PS: our developers were shocked and demanded immediate correction from us when their fool-table-scan on joined tables run four times longer on compressed tables and consumed significant amount of system CPUs.
Zavaros
 
Posts: 756
Joined: Thu Oct 24, 2002 10:50 pm
Location: Hungary


Return to Basis

Who is online

Users browsing this forum: No registered users and 3 guests





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.