2003_pesentation_Q3.pptVIP

IOUG-A “Live 2003” Quick Tips #3 Table Compression Do’s and Don’ts Tim Gorman (tim@) Principal SageLogix, Inc. 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 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 … 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 How does it work? All actual data values are stored in the block’s 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 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 … SQL*Loader DIRECT=TRUE Conventional INSERT operations unaffected SELECT, UPDATE, and DELETE behavior also unaffected 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 shou