Quick Tips

1
IOUG-A Live 2003

• Quick Tips 3
• Table Compression
• Dos and Donts
• Tim Gorman
• (tim_at_sagelogix.com)
• Principal
• SageLogix, Inc.

2
Syntax

• Available in Oracle9i Release 2 (v9.2.0.x)
• Physical storage attribute for tables and
  materialized views
• CREATE ALTER TABLE
• COMPRESS NOCOMPRESS
• Can be used for RANGE or LIST partitions
• But cannot be used with HASH partitions
• But cannot be used for HASH or LIST
  sub-partitions
• Can be specified for NESTED tables
• But cannot be used with any LOB construct
• Such as CLOB, BLOB, BFILE, and VARRAY
• Not valid for index-organized or external tables

3
Syntax

• Can be set as a DEFAULT attribute on tablespaces
• Affects both tables and indexes in the tablespace
• Unless otherwise specified
• CREATE ALTER TABLESPACE
• DEFAULT COMPRESS NOCOMPRESS STORAGE

4
How does it work?

• Storing repeated data values once in each block
• A symbol table of data values created in each
  block
• The symbol table is stored as another table in
  the block
• Each column in a row in a block references back
  to an entry in the symbol table in the block

Header Tailer
ITL
Table Column Map
Free
Symbol table
Row data
5
How does it work?

• All actual data values are stored in the blocks
  symbol table
• Each column within each row references the entry
  in the symbol table with the data value
• Data-value distribution is crucial!
• Many distinct and non-repeating data values do
  not achieve good compression
• Repeating data values achieve excellent
  compression
• Know thine data

6
Impact on DML

• Bulk-loading INSERT operations perform
  compression
• CREATE TABLE AS SELECT
• INSERT / APPEND / (single-threaded and
  parallel)
• ALTER TABLE MOVE
• ALTER TABLE MOVE PARTITION
• ALTER TABLE MERGE PARTITION
• ALTER TABLE SPLIT PARTITION
• SQLLoader DIRECTTRUE
• Conventional INSERT operations unaffected
• SELECT, UPDATE, and DELETE behavior also
  unaffected

7
Impact on DML

• SELECT
• No performance differences observed
• Testing using repeating and non-repeating data
• Theoretically, some performance improvement
  should be expected due to performing less I/O
• FULL table scans should benefit more
• Indexed scans should not benefit at all
• probable performance decrease
• INSERT
• Up to 6-8x performance degradation observed
• During testing with repeating data
• Only 2-3x performance degradation observed with
  non-repeating data
• Theoretically, some performance improvement
  should be expected due to performing less I/O

8
Impact on DML

• UPDATE
• Up to 6-8x negative performance impact observed
• Only when updating repeating data values
• Zero performance impact observed updating
  non-repeating data values
• Timings and logical-reads consistent with
  NOCOMPRESS scenarios
• DELETE
• About 2x negative performance impact observed
• Only when deleting repeating data values
• Zero performance impact observed deleting
  non-repeating data values
• Timings and logical-reads consistent with
  NOCOMPRESS scenarios

9
Usage Scenarios

• Altering a table to compress newly inserted
  blocks
• SQLgt alter table sales compress
• Table altered.
• So what would happen here?
• Would data in all existing blocks be compressed?
• Would data in previously-unused blocks be
  compressed, from now on?
• Are there any other implications?
• Indexes?
• Dependent compiled objects?

10
Usage Scenarios

• Moving a table to compress all rows
• SQLgt alter table sales move compress
• Table altered.
• So what would happen here?
• Would data in all existing blocks be compressed?
• Would data in previously-unused blocks be
  compressed, from now on?
• Are there any other implications?
• Indexes?
• Dependent compiled objects?
• Dont forget DBMS_REDEFINITION as an alternative

11
Usage Scenarios

• Compressing and moving partitions before setting
  a tablespace to READ ONLY
• SQLgt alter table sales
• 2 move partition p199801
• 3 tablespace ro_p1998q1
• 4 compress nologging parallel
• Table altered.
• SQLgt alter tablespace ro_p1998q1 read only
• Tablespace altered.

12
Usage Scenarios

• Compressing and merging partitions before setting
  a tablespace to READ ONLY
• SQLgt alter table sales
• 2 merge partition p199801, p199802, p199803
• 3 into partition p1998q1
• 4 tablespace ro_p1998q1
• 5 compress nologging parallel
• Table altered.
• SQLgt alter tablespace ro_p1998q1 read only
• Tablespace altered.

13
Warnings

• Local partitioned indexes are marked UNUSABLE
  during compression
• Includes indexes on non-partitioned tables
• Must be rebuilt
• Global partitioned indexes can be maintained
  using UPDATE GLOBAL INDEXES
• Includes non-partitioned indexes on partitioned
  tables
• A rare situation when GLOBAL indexes can be more
  highly available than LOCAL indexes!

14
Summary

• Summary
• For read-mostly objects, compression can have
  space consumption benefits
• Glitches
• None documented in MetaLink as of April 2003
• Documentation
• None documented in MetaLink as of April 2003
• OTN has a very good, detailed white paper on
  table compression

15
QA
Quick Tips 3 Slides downloadable
from http//www.EvDBT.com/papers.htm And
http//www.SageLogix.com Tim Gorman