使用compress压缩表
<DIV>-- 创建表</DIV><DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">create</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> TB_CP_TEST<BR></LI>
<LI><SPAN style="COLOR: #0000cc">(</SPAN><BR></LI>
<LI> ID <SPAN style="COLOR: #0000ff">NUMBER</SPAN> <SPAN style="COLOR: #0000ff">not</SPAN> <SPAN style="COLOR: #0000ff">null</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> NAME1 <SPAN style="COLOR: #0000ff">CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>2000<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> NAME2 <SPAN style="COLOR: #0000ff">CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>2000<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> NAME3 <SPAN style="COLOR: #0000ff">CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>2000<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> NAME4 <SPAN style="COLOR: #0000ff">CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>2000<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> NAME5 <SPAN style="COLOR: #0000ff">CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>2000<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> NAME6 <SPAN style="COLOR: #0000ff">CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>2000<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> NAME7 <SPAN style="COLOR: #0000ff">CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>2000<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">default</SPAN> <SPAN style="COLOR: #ff00ff">'A'</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000cc">)</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV></DIV>
<P>-- 写入数据</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000">Declare<BR></LI>
<LI><SPAN style="COLOR: #0000ff">Begin</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">For</SPAN> i <SPAN style="COLOR: #0000ff">In</SPAN> 1 <SPAN style="COLOR: #0000cc">.</SPAN><SPAN style="COLOR: #0000cc">.</SPAN> 10000 Loop<BR></LI>
<LI> <SPAN style="COLOR: #0000ff">Insert</SPAN> <SPAN style="COLOR: #0000ff">Into</SPAN> Tb_Cp_Test <SPAN style="COLOR: #0000cc">(</SPAN>Id<SPAN style="COLOR: #0000cc">)</SPAN> <SPAN style="COLOR: #0000ff">Values</SPAN> <SPAN style="COLOR: #0000cc">(</SPAN>i<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> Commit<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">End</SPAN> Loop<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">End</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000cc">/</SPAN></SPAN></LI></OL></DIV>
<P>-- 查看表占用空间大小</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">Select</SPAN> a<SPAN style="COLOR: #0000cc">.</SPAN>BYTES<BR></LI>
<LI><SPAN style="COLOR: #0000ff">From</SPAN> dba_segments a<BR></LI>
<LI><SPAN style="COLOR: #0000ff">Where</SPAN> a<SPAN style="COLOR: #0000cc">.</SPAN>segment_name <SPAN style="COLOR: #0000cc">=</SPAN> <SPAN style="COLOR: #ff00ff">'TB_CP_TEST'</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI>BYTES<BR></LI>
<LI><SPAN style="COLOR: #ff9900">----------<BR></LI>
<LI></SPAN><BR></LI>
<LI> 251658240</SPAN></LI></OL></DIV>
<P>-- 将表修改为可以压缩</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">Alter</SPAN> <SPAN style="COLOR: #0000ff">Table</SPAN> TB_CP_TEST <SPAN style="COLOR: #0000ff">compress</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV>
<P>-- 压缩表</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">alter</SPAN> <SPAN style="COLOR: #0000ff">table</SPAN> TB_CP_TEST move <SPAN style="COLOR: #0000ff">compress</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV>
<P>-- 查看表压缩后占用空间大小</P>
<P>压缩前后占用空间大小分别是251,658,240 150,994,944 可见压缩能节省不少空间,但表压缩后对表进行DML语句会消耗更多的io和cpu,同时表压缩后,索引会失效。</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">Select</SPAN> a<SPAN style="COLOR: #0000cc">.</SPAN>BYTES<BR></LI>
<LI><SPAN style="COLOR: #0000ff">From</SPAN> dba_segments a<BR></LI>
<LI><SPAN style="COLOR: #0000ff">Where</SPAN> a<SPAN style="COLOR: #0000cc">.</SPAN>segment_name <SPAN style="COLOR: #0000cc">=</SPAN> <SPAN style="COLOR: #ff00ff">'TB_CP_TEST'</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI>BYTES<BR></LI>
<LI><SPAN style="COLOR: #ff9900">----------<BR></LI>
<LI></SPAN><BR></LI>
<LI>150994944</SPAN></LI></OL></DIV>
<P>11gR2以后的版本可以通过dbms_compression包来查看压缩后的比例.</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">Create</SPAN> <SPAN style="COLOR: #0000ff">Table</SPAN> tb_compress_test<BR></LI>
<LI><SPAN style="COLOR: #0000ff">As</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">Select</SPAN> <SPAN style="COLOR: #0000cc">*</SPAN> <SPAN style="COLOR: #0000ff">From</SPAN> dba_objects<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI><SPAN style="COLOR: #0000ff">Alter</SPAN> <SPAN style="COLOR: #0000ff">Table</SPAN> tb_compress_test Move <SPAN style="COLOR: #0000ff">Compress</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI>DECLARE<BR></LI>
<LI> blkcnt_comp PLS_INTEGER<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> blkcnt_uncm PLS_INTEGER<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> row_comp PLS_INTEGER<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> row_uncm PLS_INTEGER<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> comp_ratio <SPAN style="COLOR: #0000ff">number</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> comp_type <SPAN style="COLOR: #0000ff">VARCHAR2</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>30<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">BEGIN</SPAN><BR></LI>
<LI> dbms_compression<SPAN style="COLOR: #0000cc">.</SPAN>get_compression_ratio<SPAN style="COLOR: #0000cc">(</SPAN><BR></LI>
<LI> scratchtbsname <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TEST'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <BR></LI>
<LI> ownname <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'HXL'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <BR></LI>
<LI> tabname <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #ff00ff">'TB_COMPRESS_TEST'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <BR></LI>
<LI> partname <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> <SPAN style="COLOR: #0000ff">NULL</SPAN><SPAN style="COLOR: #0000cc">,</SPAN> <BR></LI>
<LI> comptype <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> dbms_compression<SPAN style="COLOR: #0000cc">.</SPAN>comp_for_oltp<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> blkcnt_cmp <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> blkcnt_comp<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> blkcnt_uncmp <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> blkcnt_uncm<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> row_cmp <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> row_comp<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> row_uncmp <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> row_uncm<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> cmp_ratio <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> comp_ratio<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI> comptype_str <SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #0000cc">></SPAN> comp_type<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> dbms_output<SPAN style="COLOR: #0000cc">.</SPAN>put_line<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'Block Count Compressed: '</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN> <SPAN style="COLOR: #ff0000">TO_CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>blkcnt_comp<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> dbms_output<SPAN style="COLOR: #0000cc">.</SPAN>put_line<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'Block Count UnCompressed: '</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN> <SPAN style="COLOR: #ff0000">TO_CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>blkcnt_uncm<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> dbms_output<SPAN style="COLOR: #0000cc">.</SPAN>put_line<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'Row Count Compressed: '</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN> <SPAN style="COLOR: #ff0000">TO_CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>row_comp<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> dbms_output<SPAN style="COLOR: #0000cc">.</SPAN>put_line<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'Row Count UnCompressed: '</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN> <SPAN style="COLOR: #ff0000">TO_CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>row_uncm<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> dbms_output<SPAN style="COLOR: #0000cc">.</SPAN>put_line<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'Block Count Compressed: '</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN> <SPAN style="COLOR: #ff0000">TO_CHAR</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>comp_ratio<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> dbms_output<SPAN style="COLOR: #0000cc">.</SPAN>put_line<SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'Compression Type: '</SPAN> <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN> comp_type<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI> <SPAN style="COLOR: #0000ff">END</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI>Block <SPAN style="COLOR: #ff0000">Count</SPAN> Compressed<SPAN style="COLOR: #0000cc">:</SPAN> 353<BR></LI>
<LI>Block <SPAN style="COLOR: #ff0000">Count</SPAN> UnCompressed<SPAN style="COLOR: #0000cc">:</SPAN> 1091<BR></LI>
<LI><SPAN style="COLOR: #0000ff">Row</SPAN> <SPAN style="COLOR: #ff0000">Count</SPAN> Compressed<SPAN style="COLOR: #0000cc">:</SPAN> 216<BR></LI>
<LI><SPAN style="COLOR: #0000ff">Row</SPAN> <SPAN style="COLOR: #ff0000">Count</SPAN> UnCompressed<SPAN style="COLOR: #0000cc">:</SPAN> 69<BR></LI>
<LI>Block <SPAN style="COLOR: #ff0000">Count</SPAN> Compressed<SPAN style="COLOR: #0000cc">:</SPAN> 3<BR></LI>
<LI>Compression <SPAN style="COLOR: #0000ff">Type</SPAN><SPAN style="COLOR: #0000cc">:</SPAN> <SPAN style="COLOR: #ff00ff">"Compress For OLTP"</SPAN></SPAN></LI></OL></DIV>
<P> </P>
<P> </P>
<P> </P>
页:
[1]