Using Table Compression to Save Storage Costs

Purpose

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

Time to Complete

Approximately 20 minutes

Topics

This tutorial discusses the following:

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

Viewing Screenshots

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

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

Overview

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

Back to Topic List

Prerequisites

Before starting this tutorial, you should first complete the following steps:

1.

Install Oracle Database 11g.

2.

Download and unzip the compress.zip file into a working directory.

Back to Topic List

Granting Privileges to the SH User

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 as the sys user and execute the setup.sql script.

                                 
sqlplus / as sysdba
@setup
                              

Move your mouse over this icon to see the image


Back to Topic List

Comparing Storage Requirements

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

You can compare the storage requirements between a compressed table and an ucompressed table. Perform the following steps:

1.

You first create two copies of the SALES table, the first being compressed and the second being uncompressed. From your SQL*Plus session, execute the create_sales_tbls.sql script:

                               
@create_sales_tbls
                            

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

 

2.

Now you can compare the storage requirements between the two tables you just created. From your SQL*Plus session, execute the examine_storage.sql script:

@examine_storage

Move your mouse over this icon to see the image

 

3.

Even though you would expect that modifying a compressed table's data to be significantly slower than the same modification on an uncompressed table, there is a minimal difference. From your SQL*Plus session, execute the dml_perf.sql script:

@dml_perf

Move your mouse over this icon to see the image

 

Back to Topic List

Cleanup

To clean up your environment, perform the following step:

1.

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

@cleanup

 

Back to Topic List

Summary

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

Back to Topic List

 Move your mouse over this icon to hide all screenshot

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document