Compressing Data for Direct Path Loading
You’re working with a decision support system (DSS)-type database and you want to improve the
performance of an associated reporting application. This environment contains large tables that are
loaded once and then frequently subjected to full table scans. You want to compress data as it is loaded
because this will compact the data into fewer database blocks and thus will require less I/O for
subsequent reads from the table. Because fewer blocks need to be read for compressed data, this will
improve data retrieval performance.
Use Oracle’s basic compression feature to compress direct path–loaded data into a heap-organized
table. Basic compression is enabled as follows:
1. Use the COMPRESS clause to enable compression either when creating, altering,
or moving an existing table.
2. Load data via a direct path mechanism such as CREATE TABLE…AS SELECT or
INSERT /*+ APPEND */.
Here’s an example that uses the CREATE TABLE…AS SELECT statement to create a basic compressionenabled
table and direct path–load the data:
CREATE TABLE USER_LOG1 COMPRESS
AS SELECT * FROM USER_LOG;
The prior statement creates a table with compressed data in it. Any subsequent direct path–load
operations will also load the data in a compressed format.
You can verify that compression has been enabled for a table by querying the appropriate
DBA/ALL/USER_TABLES view. This example assumes that you’re connected to the database as the owner of
the table:
SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR
FROM USER_TABLES
WHERE TABLE_NAME='USER_LOG1';
TABLE_NAME COMPRESSION COMPRESS_FOR
USER_LOG1 ENABLED BASIC
This picture shows difference of volume consumed by original table (with no compression) and new table with compression enabled.
We checked query time for both tables and you can see the difference
SQL> set timing on
SQL> select count(*) from ftest.USER_LOG1;
COUNT(*)
----------
5209850
Elapsed: 00:00:09.40
SQL> select count(*) from ftest.USER_LOG;
COUNT(*)
----------
5209850
Elapsed: 00:00:35.62
SQL>
Table with compression retreived data in 9 seconds while original table without compression took 35 seconds for same number of records.
The prior output shows that compression has been enabled in the basic mode for this table. If
you’re working with a table has that already been created, then you can alter its basic compression
characteristics with the ALTER TABLE statement—for example:
SQL> ALTER TABLE USER_LOG1 COMPRESS;
When you alter a table to enable basic compression, this does not affect any data currently existing
in the table; rather it only compresses subsequent direct path data load operations.
If you want to enable basic compression for data in an existing table, use the MOVE COMPRESS clause:
SQL> alter table USER_LOG1 move compress;
Keep in mind that when you move a table, all of the associated indexes are invalidated. You’ll have
to rebuild any indexes associated with the moved table.
If you have enabled basic compression for a table, you can disable it via the NOCOMPRESS clause—for
example:
SQL> ALTER TABLE USER_LOG1 NOCOMPRESS;
When you alter a table to disable basic compression, this does not uncompress existing data within
the table. Rather this operation instructs Oracle to not compress data for subsequent direct path
operations. If you need to uncompress existing compressed data, then use the MOVE NOCOMPRESS clause:
SQL> ALTER TABLE USER_LOG1 MOVE NOCOMPRESS;
How It Works
The basic compression feature is available at no extra cost with the Oracle Enterprise Edition. Any heaporganized
table that has been created or altered to use basic compression will be a candidate for data
loaded in a compressed format for subsequent direct path–load operations. There is some additional
CPU overhead associated with compressing the data, but you may find in many circumstances that this
overhead is offset by performance gains due to less I/O.
From a performance perspective, the main advantage to using basic compression is that once the
data is loaded as compressed, any subsequent I/O operations will use fewer resources because there are
fewer blocks required to read and write data. You will need to test the performance benefits for your
environment. In general, tables that hold large amounts of character data are candidates for basic
compression—especially in scenarios where data is direct path–loaded once, and thereafter selected
from many times.
Keep in mind that Oracle’s basic compression feature has no effect on regular DML statements such
as INSERT, UPDATE, MERGE, and DELETE. If you require compression to occur on all DML statements, then
consider using OLTP compression (see Recipe 1-14 for details).
You can also specify basic compression at the partition and tablespace level. Any table created
within a tablespace created with the COMPRESS clause will have basic compression enabled by default.
Here’s an example of creating a tablespace with the COMPRESS clause:
CREATE TABLESPACE comp_data
DATAFILE '/ora01/dbfile/O11R2/comp_data01.dbf'
SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS;
You can also alter an existing tablespace to set the default degree of compression:
SQL> ALTER TABLESPACE COMP_DATA DEFAULT COMPRESS;
Run this query to verify that basic compression for a tablespace is enabled:
SELECT TABLESPACE_NAME, DEF_TAB_COMPRESSION, COMPRESS_FOR
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'COMP_DATA';
Here is some sample output:
TABLESPACE_NAME DEF_TAB_ COMPRESS_FOR
------------------------------ -------- ------------
COMP_DATA ENABLED BASIC
***********************************************************
You’re in an OLTP environment and have noticed that there is a great deal of disk I/O occurring when
reading data from a table. You wonder if you can increase I/O performance by compressing the data
within the table. The idea is that compressed table data will consume less physical storage and thus
require less I/O to read from disk.
Solution
Use the COMPRESS FOR OLTP clause when creating a table to enable data compression when using regular DML statements to manipulate data. This example creates an OLTP compression–enabled table:
CREATE TABLE REGS
(REG_ID NUMBER
,REG_NAME VARCHAR2(2000)
) COMPRESS FOR OLTP;
You can verify that compression has been enabled for a table by querying the appropriate
DBA/ALL/USER_TABLES view. This example assumes that you’re connected to the database as the owner of the table:
SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR
FROM USER_TABLES
WHERE TABLE_NAME='REGS';
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
REGS ENABLED OLTP
If you’ve already created the table, you can use the ALTER TABLE statement to enable compression on
an existing table—for example:
SQL> ALTER TABLE REGS COMPRESS FOR OLTP;
When you alter a table’s compression mode, it doesn’t impact any of the data currently within the
table. Subsequent DML statements will result in data stored in a compressed fashion.
If you want to enable OLTP compression for data in an existing table, use the MOVE COMPRESS FOR
OLTP clause:
SQL>ALTER TABLE REGS MOVE COMPRESS FOR OLTP;
Keep in mind that when you move a table, all of the associated indexes are invalidated. You’ll have
to rebuild any indexes associated with the moved table.
If you have enabled OLTP compression for a table, you can disable it via the NOCOMPRESS clause—for
example:
SQL> ALTER TABLE REGS NOCOMPRESS;
When you alter a table to disable OLTP compression, this does not uncompress existing data within
the table. Rather this operation instructs Oracle to not compress data for subsequent DML operations.
How It Works
OLTP compression requires the Oracle Enterprise Edition and the Advanced Compression Option (extra
cost license). The COMPRESS FOR OLTP clause enables compression for all DML operations. The OLTP compression doesn’t immediately compress data as it is inserted or updated in a table. Rather the
compression occurs in a batch mode when the degree of change within the block reaches a certain
threshold. When the threshold is reached, all of the uncompressed rows are compressed at the same
time. The threshold at which compression occurs is determined by an internal algorithm (over which
you have no control).
You can also specify OLTP compression at the tablespace level. Any table created in an OLTP
compression–enabled tablespace will by default inherit this compression setting. Here’s an example of
tablespace creation script specifying OLTP compression:
CREATE TABLESPACE comp_data
DATAFILE '/ora01/dbfile/O11R2/comp_data01.dbf'
SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT COMPRESS FOR OLTP;
You can also alter an existing tablespace to set the default degree of compression:
SQL> ALTER TABLESPACE COMP_DATA DEFAULT COMPRESS FOR OLTP;
You can verify the default compression characteristics with this query:
SELECT TABLESPACE_NAME, DEF_TAB_COMPRESSION, COMPRESS_FOR
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'COMP_DATA';
Here is some sample output:
TABLESPACE_NAME DEF_TAB_ COMPRESS_FOR
------------------------------ -------- ------------
COMP_DATA ENABLED OLTP
****************************************************
Compressing Data at the Column level
****************************************************If You’re using the Oracle Exadata product and you want to efficiently compress data. You have
determined that compressed data will result in much more efficient I/O operations, especially when
reading data from disk. The idea is that compressed data will result in much fewer blocks read for SELECT
statements.
To enable hybrid columnar compression, when creating a table, use either the COMPRESS FOR QUERY or the COMPRESS FOR ARCHIVE clause—for example:
CREATE TABLE F_REGS(
REG_ID NUMBER
,REG_DESC VARCHAR2(4000))
COMPRESS FOR QUERY;
You can also specify a degree of compression of either LOW or HIGH:
CREATE TABLE F_REGS(
REG_ID NUMBER
,REG_DESC VARCHAR2(4000))
COMPRESS FOR QUERY HIGH;
The default level of compression for QUERY is HIGH, and the default level of compression for ARCHIVE is LOW. You can validate the level of compression enabled via this query:
SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR
FROM USER_TABLES
WHERE TABLE_NAME='F_REGS';
Here is some sample output:
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
F_REGS ENABLED QUERY HIGH
If you attempt to use hybrid columnar compression in an environment other than Exadata, you’ll
receive the following error:
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage
No comments:
Post a Comment