Oracle 压缩表类型测试

g_hk 发表于 2011-12-23 03:46

The <code>DBMS_COMPRESSION</code> package uses the constants shown in <a href="http://oracle.su/docs/11g/appdev.112/e10577/d_compress.htm#BEIBFCCB">Table 34-1, "DBMS_COMPRESSION Constants - Compression Types"</a>e:
<div class="tblformal"><a id="ARPLS72895" name="ARPLS72895"></a><a id="sthref1621" name="sthref1621"></a><a id="BEIBFCCB" name="BEIBFCCB"></a>
Table 34-1 DBMS_COMPRESSION Constants - Compression Types
<table class="Formal" title="DBMS_COMPRESSION Constants - Compression Types" summary="This table describes DBMS_DBFS_CONTENT Constants - Chunk Status Value" dir="ltr" frame="hsides" rules="groups" border="1" cellpadding="3" cellspacing="0" width="100%">
<colgroup><col width="31%">
<col width="16%">
<col width="19%">
<col width="*">
</colgroup><thead>
<tr align="left" valign="top">
<th id="r1c1-t3" align="left" valign="bottom">Constant</th>
<th id="r1c2-t3" align="left" valign="bottom">Type</th>
<th id="r1c3-t3" align="left" valign="bottom">Value</th>
<th id="r1c4-t3" align="left" valign="bottom">Description</th>
</tr>
</thead>
<tbody>
<tr align="left" valign="top">
<td id="r2c1-t3" headers="r1c1-t3" align="left">
<code>COMP_NOCOMPRESS</code>
</td>
<td headers="r2c1-t3 r1c2-t3" align="left">
<code>NUMBER</code>
</td>
<td headers="r2c1-t3 r1c3-t3" align="left">
<code>1</code>
</td>
<td headers="r2c1-t3 r1c4-t3" align="left">
No compression
</td>
</tr>
<tr align="left" valign="top">
<td id="r3c1-t3" headers="r1c1-t3" align="left">
<code>COMP_FOR_OLTP</code>
</td>
<td headers="r3c1-t3 r1c2-t3" align="left">
<code>NUMBER</code>
</td>
<td headers="r3c1-t3 r1c3-t3" align="left">
<code>2</code>
</td>
<td headers="r3c1-t3 r1c4-t3" align="left">
OLTP compression
</td>
</tr>
<tr align="left" valign="top">
<td id="r4c1-t3" headers="r1c1-t3" align="left">
<code>COMP_FOR_QUERY_HIGH</code>
</td>
<td headers="r4c1-t3 r1c2-t3" align="left">
<code>NUMBER</code>
</td>
<td headers="r4c1-t3 r1c3-t3" align="left">
<code>4</code>
</td>
<td headers="r4c1-t3 r1c4-t3" align="left">
High compression level for query operations
</td>
</tr>
<tr align="left" valign="top">
<td id="r5c1-t3" headers="r1c1-t3" align="left">
<code>COMP_FOR_QUERY_LOW</code>
</td>
<td headers="r5c1-t3 r1c2-t3" align="left">
<code>NUMBER</code>
</td>
<td headers="r5c1-t3 r1c3-t3" align="left">
<code>8</code>
</td>
<td headers="r5c1-t3 r1c4-t3" align="left">
Low compression level for query operations
</td>
</tr>
<tr align="left" valign="top">
<td id="r6c1-t3" headers="r1c1-t3" align="left">
<code>COMP_FOR_ARCHIVE_HIGH</code>
</td>
<td headers="r6c1-t3 r1c2-t3" align="left">
<code>NUMBER</code>
</td>
<td headers="r6c1-t3 r1c3-t3" align="left">
<code>16</code>
</td>
<td headers="r6c1-t3 r1c4-t3" align="left">
High compression level for archive operations
</td>
</tr>
<tr align="left" valign="top">
<td id="r7c1-t3" headers="r1c1-t3" align="left">
<code>COMP_FOR_ARCHIVE_LOW</code>
</td>
<td headers="r7c1-t3 r1c2-t3" align="left">
<code>NUMBER</code>
</td>
<td headers="r7c1-t3 r1c3-t3" align="left">
<code>32</code>
</td>
<td headers="r7c1-t3 r1c4-t3" align="left">
Low compression level for archive operations
</td>
</tr>
</tbody>
</table>
 </div>

<div class="infoboxnote">
Note:
The constants <code>COMP_FOR_QUERY_HIGH</code>, <code>COMP_FOR_QUERY_LOW</code>, <code>COMP_FOR_ARCHIVE_HIGH</code>, and <code>COMP_FOR_ARCHIVE_LOW</code>
are used only in the context of Exadata, specifically the feature,
Exadata Hybrid Columnar Compression (EHCC) which offers higher
compression ratios for direct path loaded data. For more information,
see the Oracle Exadata Storage Server Online Documentation Library. More info: <a href="http://oracle.su/docs/11g/appdev.112/e10577/d_compress.htm" target="_blank">http://oracle.su/docs/11g/appdev.112/e10577/d_compress.htm</a> ---------------------------------------------------- Test Examples: SQL> alter table ct2 move compress; #看来这个操作不一定产生真正的压缩表。 Table altered. SQL> select rowid, a.* from ct2 a where rownum<2; ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASAqAAFAAAAErAAA       1513 AAAAAA1513 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2', 'AAASAqAAFAAAAErAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2','AAASAQAAFAAAAERAAA') -----------------------------------------------------------------------                                                                       1 SQL> create table ct2_1 compress as select * from ct2; Table created. SQL> select rowid,a.* from ct2_1 a where rownum<2; ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASArAAFAAAACrAAA       1513 AAAAAA1513 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2_1', 'AAASArAAFAAAACrAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2_1','AAASARAAFAAAACRAAA') -------------------------------------------------------------------------                                                                         1 SQL> insert into ct2_1 select * from ct2_1; 262144 rows created. SQL> / 524288 rows created. SQL> commit; Commit complete. SQL>  select rowid,a.* from ct2_1 a where rownum<2; ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASArAAFAAAACrAAA       1513 AAAAAA1513 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2_1', 'AAASArAAFAAAACrAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2_1','AAASARAAFAAAACRAAA') -------------------------------------------------------------------------                                                                         1 SQL> alter table ct2_1 move compress; Table altered. SQL> alter system switch logfile; System altered. SQL> select rowid,a.* from ct2_1 a where rownum<2; ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASAsAAFAAAA+CAAA       1034 AAAAAA1034 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT2_1', 'AAASAsAAFAAAA+CAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT2_1','AAASASAAFAAAA+CAAA') -------------------------------------------------------------------------                                                                         2 终于产生了一个OLT compress类型的压缩表。 再来产生一个query high类型的压缩表看看： SQL> create table ct1 compress for query high as select * from tabx; Table created. SQL> select count(*) from ct1;   COUNT(*) ----------       8192 SQL> select rowid from ct1 where rownum<2; ROWID ------------------ AAAR/QAAFAAAACjAAA SQL> select rowid from tabx where rownum<2; ROWID ------------------ AAAR/PAAFAAAACDAAA SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT1', 'AAAR/QAAFAAAACjAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT1','AAAR/QAAFAAAACJAAA') -----------------------------------------------------------------------                                                                       4 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'TABX', 'AAAR/PAAFAAAACDAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','TABX','AAAR/PAAFAAAACDAAA') ------------------------------------------------------------------------                                                                        1 query low类型的压缩表： SQL> create table ct8 compress for query low as select * from tabx; Table created. SQL> select rowid,a.* from ct8 a where rownum<2; ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASAtAAFAAAACrAAA       1513 AAAAAA1513 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT8', 'AAASAtAAFAAAACrAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT8','AAASATAAFAAAACRAAA') -----------------------------------------------------------------------                                                                       8 archive high类型压缩表： SQL> create table ct16 compress for archive high as select * from tabx; Table created. SQL>  select rowid,a.* from ct16  a where rownum<2; ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASAuAAFAAAAC7AAA          1 AAAAAA1 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT16', 'AAASAuAAFAAAAC7AAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT16','AAASAUAAFAAAAC7AAA') ------------------------------------------------------------------------                                                                       16 archive low类型压缩表： SQL> create table ct32 compress for archive low as select * from tabx; Table created. SQL> select rowid,a.* from ct16  a where rownum<2; ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASAuAAFAAAAC7AAA          1 AAAAAA1 SQL> c/16/32   1* select rowid,a.* from ct32  a where rownum<2 SQL> / ROWID                       A ------------------ ---------- B -------------------------------------------------------------------------------- AAASAvAAFAAAADDAAA          1 AAAAAA1 SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN', 'CT32', 'AAASAvAAFAAAADDAAA') from dual; DBMS_COMPRESSION.GET_COMPRESSION_TYPE('GAN','CT32','AAASAVAAFAAAADDAAA') ------------------------------------------------------------------------                                                                       32 </div>

页: [1]

Chinaunix's Archiver

Oracle 压缩表类型测试