2003 presentation q3
TRANSCRIPT
-
8/2/2019 2003 Presentation Q3
1/15
www.SageLogix.com
Quick Tips #3
Table Compression
Dos and Donts
Tim Gorman
Principal
SageLogix, Inc.
IOUG-A Live 2003IOUG-A Live 2003
-
8/2/2019 2003 Presentation Q3
2/15
www.SageLogix.com
SyntaxSyntax
Availablein Oracle9iRelease 2 (v9.2.0.x)Physicalstorageattribute fortables andmaterialized views
[ CREATE | ALTER ] TABLE
[ COMPRESS | NOCOMPRESS ]
Canbeused forRANGE orLIST partitionsButcannot beusedwith HASH partitions
Butcannot beused for HASH orLISTsub-partitions
Canbespecified for NESTED tablesButcannot beusedwithanyLOBconstruct
SuchasCLOB,BLOB,BFILE,and VARRAY
Notvalid forindex-organizedorexternaltables
-
8/2/2019 2003 Presentation Q3
3/15
www.SageLogix.com
SyntaxSyntax
Canbesetasa DEFAULTattributeontablespaces
Affectsbothtablesandindexesinthetablespace
Unlessotherwisespecified
[ CREATE | ALTER ] TABLESPACE
DEFAULT [ COMPRESS | NOCOMPRESS ]
STORAGE
-
8/2/2019 2003 Presentation Q3
4/15
www.SageLogix.com
Howdoesitwork?Howdoesitwork?
Storing repeateddatavaluesonce ineachblockA symbol table ofdatavaluescreatedineachblock
Thesymboltableisstoredasanother table intheblock
Eachcolumnina rowinablock referencesback toanentryinthesymboltableintheblock
Header &
Tailer
Table &
Column Map
ITL
Symbol table
Free
Row data
-
8/2/2019 2003 Presentation Q3
5/15
www.SageLogix.com
Howdoesitwork?Howdoesitwork?
Allactualdatavaluesarestoredintheblockssymbol table
Eachcolumnwithineach row referencestheentryinthe
symbol table withthedatavalue
Data-valuedistributioniscrucial!Manydistinctandnon-repeatingdatavaluesdonot
achievegoodcompression
Repeatingdatavaluesachieveexcellentcompression
Know thine data
-
8/2/2019 2003 Presentation Q3
6/15
www.SageLogix.com
Impacton DMLImpacton DML
Bulk-loadingINSERToperations performcompression
CREATE TABLE ASSELECT
INSERT /*+ APPEND */ (single-threadedand parallel)
ALTERTABLE MOVE
ALTERTABLE MOVE PARTITION
ALTERTABLE MERGE PARTITION
ALTERTABLE SPLITPARTITION
SQL*Loader DIRECT=TRUE
ConventionalINSERToperationsunaffected
SELECT, UPDATE,and DELETE behavioralso
unaffected
-
8/2/2019 2003 Presentation Q3
7/15
www.SageLogix.com
Impacton DMLImpacton DML
SELECTNo performancedifferencesobserved
Testingusingrepeatingandnon-repeatingdata
Theoretically,some performanceimprovementshouldbeexpected dueto performingless I/O
FULLtablescansshouldbenefitmoreIndexedscansshouldnotbenefitatall
probable performancedecrease
INSERTUp to 6-8x performancedegradationobserved
DuringtestingwithrepeatingdataOnly 2-3x performancedegradationobservedwithnon-repeatingdata
Theoretically,some performanceimprovementshouldbeexpected dueto performingless I/O
-
8/2/2019 2003 Presentation Q3
8/15
www.SageLogix.com
Impacton DMLImpacton DML
UPDATEUp to 6-8xnegative performanceimpactobserved
Only whenupdatingrepeatingdatavalues
Zero performanceimpactobservedupdatingnon-
repeatingdatavalues
Timingsandlogical-readsconsistentwith NOCOMPRESS
scenarios
DELETE
About 2xnegative performanceimpactobserved
Only whendeletingrepeatingdatavalues
Zero performanceimpactobserveddeletingnon-
repeatingdatavalues
Timingsandlogical-readsconsistentwith NOCOMPRESS
scenarios
-
8/2/2019 2003 Presentation Q3
9/15
www.SageLogix.com
UsageScenariosUsageScenarios
Alteringatabletocompressnewlyinsertedblocks
SQL> alter table sales compress;
Table altered.
Sowhatwouldhappenhere?
Woulddatainallexistingblocksbecompressed?
Woulddatain previously-unusedblocksbecompressed,
fromnowon?Arethereanyotherimplications?
Indexes?
Dependentcompiledobjects?
-
8/2/2019 2003 Presentation Q3
10/15
www.SageLogix.com
UsageScenariosUsageScenarios
Movingatabletocompressall rows
SQL> alter table sales move compress;
Table altered.
Sowhatwouldhappenhere?Woulddatainallexistingblocksbecompressed?
Woulddatain previously-unusedblocksbecompressed,fromnowon?
Arethereanyotherimplications?Indexes?
Dependentcompiledobjects?
Dont forget DBMS_REDEFINITION asanalternative
-
8/2/2019 2003 Presentation Q3
11/15
www.SageLogix.com
UsageScenariosUsageScenarios
Compressingandmoving partitionsbeforesettinga
tablespacetoREAD ONLY
SQL> alter table sales
2 move partition p1998013 tablespace ro_p1998q1
4 compress nologging parallel;
Table altered.
SQL> alter tablespace ro_p1998q1 read only;
Tablespace altered.
-
8/2/2019 2003 Presentation Q3
12/15
www.SageLogix.com
UsageScenariosUsageScenarios
Compressingandmerging partitionsbeforesettingatablespacetoREAD ONLY
SQL> alter table sales
2 merge partition p199801, p199802, p1998033 into partition p1998q1
4 tablespace ro_p1998q1
5 compress nologging parallel;
Table altered.
SQL> alter tablespace ro_p1998q1 read only;
Tablespace altered.
-
8/2/2019 2003 Presentation Q3
13/15
www.SageLogix.com
WarningsWarnings
Local partitionedindexesaremarkedUNUSABLE duringcompression
Includesindexesonnon-partitionedtables
Mustbe rebuilt
Global partitionedindexescanbemaintainedusing UPDATE GLOBAL
INDEXESIncludesnon-partitionedindexeson partitionedtables
A rare situation when GLOBAL indexes can be morehighly available than LOCAL indexes!
-
8/2/2019 2003 Presentation Q3
14/15
www.SageLogix.com
SummarySummary
Summary:For read-mostlyobjects,compressioncanhavespace
consumptionbenefits
Glitches:
NonedocumentedinMetaLink asof April 2003
Documentation:NonedocumentedinMetaLink asof April 2003
OTN hasaverygood,detailedwhite paperontable
compression
-
8/2/2019 2003 Presentation Q3
15/15
www.SageLogix.com
Q&AQ&A
Quick Tips #3
Slides downloadable from
http://www.EvDBT.com/papers.htmAnd
http://www.SageLogix.com
Tim Gorman