How To Compress Table In Oracle

Using Table Pinch to Salvage Storage Costs

This tutorial shows you lot how to realize the storage benefits of tabular array compression. In addition, you contrast performance differences between compressed tables and standard, uncompressed tables.

Approximately 20 minutes

This tutorial discusses the following:

	Overview
	Prerequisites
	Granting Privileges to the SH User
	Comparing Storage Requirements
	Cleanup
	Summary

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This activeness loads all screenshots simultaneously, so, depending on your Internet connection, may consequence in a slow response fourth dimension.)

Note: Alternatively, you can place the cursor over an private icon in the following steps to load and view only the screenshot associated with that step. Yous tin can hide an individual screenshot by clicking it.

The cost of disk systems can be a very large portion of building and maintaining big data warehouses. Oracle Database eleventhouR2 helps reduce this cost by compressing the information and it does so without the typical trade-offs of space savings versus access fourth dimension to information for normal query operations. The overhead incurred past compression is generally related to the initial compression of the information and operations that modify or change the information (DML).

Back to Topic List

Before starting this tutorial, y'all should:

1.	Install Oracle Database 11g.
2.	Download and unzip the compress.nil file into your working directory.

Back to Topic Listing

This tutorial requires that the SH user be unlocked and the necessary privileges be granted to the SH user. Perform the following:

1.	Open SQL*Plus. Login every bit the sys user and execute the setup.sql script. sqlplus / as sysdba @setup

Back to Topic List

NOTE: Your timings maybe slightly dissimilar than the timings in these screenshots.

Y'all can compare the storage requirements between a compressed table and an ucompressed tabular array. Perform the following steps:

1.	Yous first create two copies of the SALES tabular array, the first being compressed and the 2d beingness uncompressed. Login as the sh user with the sh countersign. Notation: In this practise, simple and piece of cake to call up passwords are used in order not to detract from the purpose of the practise. In real development and production environments, use strong passwords following the guidelines in Oracle Database Security Guide and the Oracle Secure Fill-in Ambassador's Guide. connect sh Password: sh (not displayed when you enter it) From your SQL*Plus session, execute the create_sales_tbls.sql script: @create_sales_tbls
2.	At present you can compare the storage requirements betwixt the 2 tables you just created. From your SQL*Plus session, execute the examine_storage.sql script: @examine_storage
3.	Even though yous would expect that modifying a compressed table's data to exist significantly slower than the same modification on an uncompressed table, at that place is a minimal difference. From your SQL*Plus session, execute the dml_perf.sql script: @dml_perf

Back to Topic Listing

To clean up your surroundings, perform the post-obit step:

ane.	From your SQL*Plus session, execute the cleanup.sql script: @cleanup

Back to Topic Listing

In this tutorial, you lot've learned how to compare storage requirements of compressed versus uncompressed tables.

Dorsum to Topic List

Motion your mouse over this icon to hide all screenshot

Source: https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/storage/compress/compress.htm

Share this post