In an earlier article I covered Hybrid Columnar Compression, an option specific to Exadata and systems using Exadata storage. Those aren't the only compression options; two others, BASIC and OLTP, are available outside of the Advanced Compression licensing and can be used on non-Exadata storage. Let's go over the HCC options again and compare those to BASIC and OLTP compression. We'll start with BASIC and OLTP compression and move into a review of the HCC types.
Oracle 11.2.0.x provides, out of the box, two types of table compression, BASIC and OLTP. BASIC is exactly what its name says, compressed with a basic compression algorithm. Let's see what space savings BASIC can provide:
SQL> select segment_name, sum(bytes) total_space2 from user_segmentsme; SEGMENT_NAME3 group by segment_n a TOTAL_SPACE- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT-- -- -- -- -- -- -- - 65536 DUMMY 65536 EMP 713031680segment_name, sum(bytes) total_space 2 fromSALGRADE 65536 SQL> alter table emp move compress; Table altered. SQL> select user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE65536 DUMMY 655-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEP T36 EMP 184549376 SALGRADE 65536SQL>
So far, so good. Let's now update some data in the EMP table and see what happens to the consumed space:
SQL> update emp set sal=5001 where sal=5000;1048576 rows updated. SQL> commit;me, sum(bytes) tCommit complete. SQL> select segment_n aotal_space 2 from user_segmentsT_NAME3 group by segment_name; SEGME N TOTAL_SPACE- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT-- -- -- -- - 65536 DUMMY 6553665536 SQL>EMP 234881024 SALGRADE
The update caused Oracle to uncompress the rows being updated and, thus increase the space consumed by the table. You can re-compress the table after updates but that could get to be a daunting task, requiring a stored procedure for inserts, updates and deletes to the affected table or tables. A second option is a scheduled job run late at night to re-compress the compressed
tables.
OLTP compression works a bit differently, as it compresses the data but will, when affected data blocks are marked as full, re-compress the block automatically. Let's look at the same table using OLTP compression. First let's uncompress the table:
SQL> alter table emp move nocompress;Table altered._name, sum(bytes) total_space 2 from user_segSQL> select segmen tments 3 group by segment_name;OTAL_SPACE -- -- -- -- -- -- -- -- --SEGMENT_NAME T-- -- -- -- -- -- -- -- - -- -- -- -- -- -65536 EMPDEPT 65536 DUMMY 645922816 SALGRADE 65536SQL>
It's interesting to note that the uncompressed table size has decreased slightly from its original value, possibly because unnecessary NULL bytes have been removed (this is a guess). Compressing for OLTP and updating the same rows produces these results:
SQL> alter table emp move compress for oltp;Table altered._name, sum(bytes) total_space 2 from user_segSQL> select segmen tments 3 group by segment_name;OTAL_SPACE -- -- -- -- -- -- -- -- --SEGMENT_NAME T-- -- -- -- -- -- -- -- - -- -- -- -- -- -65536 EMPDEPT 65536 DUMMY 184549376 SALGRADE 65536complete. SQL> select segment_name, sum(bySQL> update emp set sal=5000 where sal=5001; 1048576 rows updated. SQL> commit; Commit tes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE65536 DUMMY 65536 EMP-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 201326592 SALGRADE 65536SQL>
Notice that the compressed size of the OLTP table is initially the same as it was for BASIC compression; the difference is in the compressed size after the update. It's the OLTP compression mechanism that does this, as some of the updated blocks are now full and triggered the automatic re-compression of the data.
HCC works differently than Basic or OLTP compression as it re-organizes data into Compression Units (CU). Remember that there are two types of HCC compression, QUERY and ARCHIVE, with two levels of LOW and HIGH for each. Using the same examples from a previous article let's look at these compression types in action. First in line is QUERY:
SQL>SQL> -- SQL> -- Current storage for the EMP tableSQL> -- (this is simply a test table for this example)SQL> -- SQL> select segment_name, sum(bytes) total_space2 from user_segments 3 group by segment_name;- -- -- -- -- -- -- -- -- -- -- -- -SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- - -- -- -- -- -- - DEPT 65536713031680 EMP_IDXDUMMY 65536 EMP 478150656 SALGRADE 65536 Elapsed: 00:00:00.82 SQL>time to compress SQL> -- SQL> alter table emp move compressSQL> -- SQL> -- Compress the table for query high (use HCC) SQL> -- SQL> -- Note elapsed for query high; Table altered. Elapsed: 00:00:35.65 SQL> SQL> -- SQL> -- Index is now invalid SQL> -- SQL> -- Must rebuild to make it usablempression type, storage for tableSQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter index emp_idx rebuild; Index altered. Elapsed: 00:01:13.70 SQL> SQL> -- SQL> -- Current c o/index SQL> -- initially after compression is enabled SQL> -- SQL> select table_name, compression, compress_for 2 from user_tables;-- -- -- --TABLE_NAME COMPRESS COMPRESS_FOR -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --DEPT DISABLEDBONUS DISABLEDDUMMY DISABLEDSALGRADE DISABLEDQUERY HIGH Elapsed: 00:00:00.20 SQL> SQL> selecEMP ENABLED t segment_name, sum(bytes) total_space 2 from user_segmentsTOTAL_SPACE -- -- --3 group by segment_name; SEGMENT_NAME -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- -65536 EMPDEPT 65536 DUMMY 8388608 EMP_IDX 260046848 SALGRADE 65536Elapsed: 00:00:00.03SQL>
The resulting size is much smaller than either of the Basic or OLTP compression options. Even at the Query Low compression rate the size is still less than OLTP compression can provide:
SQL>SQL> -- SQL> -- Initial storageSQL> -- SQL> select segment_name, sum(bytes) total_space2 from user_segments 3 group by segment_name;- -- -- -- -- -- -- -- -- -- -- -- -SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- - -- -- -- -- -- - DEPT 65536713031680 EMP_IDXDUMMY 65536 EMP 478150656 SALGRADE 65536 Elapsed: 00:00:00.25 SQL> SQL> -- SQL> -- Compress for QUERY LOW.16 SQL> SQL> alter index emp_idSQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter table emp move compress for query low; Table altered. Elapsed: 00:00:1 6x rebuild; Index altered. Elapsed: 00:00:43.08 SQL> SQL> -- SQL> -- These figures are the same as those generatedor 2 from user_tables; TABLE_NAME COMPRSQL> -- AFTER the HCC compressed data was updated the first time SQL> -- SQL> select table_name, compression, compress_ fESS COMPRESS_FOR -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DEPT DISABLEDQUERY LOW Elapsed: 00:00:00.02 SQL>BONUS DISABLED SALGRADE DISABLED DUMMY DISABLED EMP ENABLED SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE65536 EMP 14680064 EMP_IDX-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 260046848 SALGRADE 65536 Elapsed: 00:00:00.02SQL>
The QUERY compression type is definitely a space saver, but note that after updates to a table compressed with either QUERY LOW or QUERY HIGH the compression behavior reverts to the OLTP re-compression mechanism.
The HCC compression type ARCHIVE is definitely the most aggressive in terms of space savings, but it's also intended for data that is designated read-only and is or will be archived for occasional use:
SQL> -- SQL> -- Current storage for the EMP tableSQL> -- (this is simply a test table for this example)SQL> -- SQL> select segment_name, sum(bytes) total_space2 from user_segments 3 group by segment_name;- -- -- -- -- -- -- -- -- -- -- -- -SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- - -- -- -- -- -- - DEPT 65536713031680 EMP_IDXDUMMY 65536 EMP 478150656 SALGRADE 65536 Elapsed: 00:00:00.02 SQL>compress SQL> -- SQL> alter table emp move compressSQL> -- SQL> -- Compress the table for archive high SQL> -- SQL> -- Note elapsed time to for archive high; Table altered. Elapsed: 00:00:38.55 SQL> SQL> -- SQL> -- Index is now invalidex emp_idx rebuild; Index altered. ElapsedSQL> -- SQL> -- Must rebuild to make it usable SQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter in d: 00:00:39.45 SQL> SQL> -- SQL> -- Current compression type, storage for table/index SQL> -- initially after compression is enabledS_FOR -- -- -- -- -- -- -- -- -- -- -- -- --SQL> -- SQL> select table_name, compression, compress_for 2 from user_tables; TABLE_NAME COMPRESS COMPRE S -- -- -- -- -- -- -- -- -- -- -- -- DEPT DISABLED BONUS DISABLED SALGRADE DISABLED2 from user_segments 3 group by sDUMMY DISABLED EMP ENABLED ARCHIVE HIGH Elapsed: 00:00:00.02 SQL> SQL> select segment_name, sum(bytes) total_space egment_name;DUMMY 65536SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 6553 6 EMP 4194304 EMP_IDX 260046848 SALGRADE 65536 Elapsed: 00:00:00.01SQL>
The space savings are substantial, taking the table from its original size of 680 megabytes down to 4 megabytes, a savings of 99.41 percent. Using ARCHIVE LOW instead of ARCHIVE HIGH still produces impressive results:
SQL> -- SQL> -- Initial storageSQL> -- SQL> select segment_name, sum(bytes) total_space2 from user_segments 3 group by segment_name;- -- -- -- -- -- -- -- -- -- -- -- -SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- - -- -- -- -- -- - DEPT 65536713031680 EMP_IDXDUMMY 65536 EMP 478150656 SALGRADE 65536 Elapsed: 00:00:01.31 SQL> SQL> -- SQL> -- Compress for ARCHIVE LOW.16 SQL> SQL> alter index emp_idSQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter table emp move compress for archive low; Table altered. Elapsed: 00:00:3 4x rebuild; Index altered. Elapsed: 00:00:48.44 SQL> SQL> -- SQL> -- These figures are the same as those generatedor 2 from user_tables; TABLE_NAME COMPRSQL> -- AFTER the HCC compressed data was updated the first time SQL> -- SQL> select table_name, compression, compress_ fESS COMPRESS_FOR -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DEPT DISABLEDARCHIVE LOW Elapsed: 00:00:00.03 SQLBONUS DISABLED SALGRADE DISABLED DUMMY DISABLED EMP ENABLED > SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE65536 EMP 8388608 EMP_IDX-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 260046848 SALGRADE 65536 Elapsed: 00:00:00.02SQL>
Using ARCHIVE LOW the table ends up twice as large as it did using ARCHIVE HIGH but the table is 98.82 percent smaller than it was before compression. This is the same level of compression afforded by QUERY HIGH. The same caveat for QUERY level compression also applies to ARCHIVE; updates to a table compressed either ARCHIVE LOW or ARCHIVE HIGH cause Oracle to revert to the OLTP re-compression mechanism. Also note that the reported compression type in the data dictionary does NOT change when this occurs.
Compression can be an excellent tool to save database storage, but you need to be aware that the compression levels can change for updated tables when running Exadata and using any of the HCC compression types. Also it's good to be aware that BASIC compression won't automatically re-compress updated data; maintaining a reasonable compression ratio will require some sort of manual or scheduled intervention, so using OLTP compression on active tables is the best choice if you are planning on using table compression.
0 komentar:
Posting Komentar