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.
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 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.
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).
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. |
|
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
|
|
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
|
| 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
|
| 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
|
To clean up your environment, perform the following step:
| 1. | From your SQL*Plus session, execute the cleanup.sql script: @cleanup
|
In this tutorial, you've learned how to compare storage requirements of compressed versus uncompressed tables.
Move your mouse over this icon to hide all screenshot