hxl 发表于 2011-12-23 03:49

Exchange交换分区索引

<DIV>Exchange分区的时候,同时可以交换索引,分区表的索引必须是本地索引.</DIV>
<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_hxl_hash<BR></LI>
<LI><SPAN style="COLOR: #0000cc">(</SPAN><BR></LI>
<LI>&nbsp;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>&nbsp;cnt <SPAN style="COLOR: #0000ff">Number</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000cc">)</SPAN><BR></LI>
<LI>Partition <SPAN style="COLOR: #0000ff">By</SPAN> Hash<SPAN style="COLOR: #0000cc">(</SPAN>Id<SPAN style="COLOR: #0000cc">)</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000cc">(</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h1<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h2<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h3<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h4<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h5<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h6<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h7<SPAN style="COLOR: #0000cc">,</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Partition p_h8<BR></LI>
<LI><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI><BR></LI>
<LI>Declare<BR></LI>
<LI><SPAN style="COLOR: #0000ff">Begin</SPAN><BR></LI>
<LI>&nbsp;&nbsp;<SPAN style="COLOR: #0000ff">For</SPAN> i <SPAN style="COLOR: #0000ff">In</SPAN> 1 <SPAN style="COLOR: #0000cc">.</SPAN><SPAN style="COLOR: #0000cc">.</SPAN> 100000 Loop<BR></LI>
<LI>&nbsp;&nbsp;&nbsp;&nbsp;<SPAN style="COLOR: #0000ff">Insert</SPAN> <SPAN style="COLOR: #0000ff">Into</SPAN> Tb_Hxl_Hash <SPAN style="COLOR: #0000ff">Values</SPAN> <SPAN style="COLOR: #0000cc">(</SPAN>i<SPAN style="COLOR: #0000cc">,</SPAN> i <SPAN style="COLOR: #0000cc">*</SPAN> 10<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI>&nbsp;&nbsp;<SPAN style="COLOR: #0000ff">End</SPAN> Loop<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI>&nbsp;&nbsp;Commit<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><BR></LI>
<LI><BR></LI>
<LI><BR></LI>
<LI><SPAN style="COLOR: #0000ff">Create</SPAN> <SPAN style="COLOR: #0000ff">Unique</SPAN> <SPAN style="COLOR: #0000ff">Index</SPAN> idx_Tb_Hxl_Hash_u1 <SPAN style="COLOR: #0000ff">On</SPAN> Tb_Hxl_Hash<SPAN style="COLOR: #0000cc">(</SPAN>Id<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI><BR></LI>
<LI><SPAN style="COLOR: #0000ff">Create</SPAN> <SPAN style="COLOR: #0000ff">Table</SPAN> Tb_Hxl_Hash_mid<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> Tb_Hxl_Hash <SPAN style="COLOR: #0000ff">Where</SPAN> 1<SPAN style="COLOR: #0000cc">=</SPAN>2<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI><BR></LI>
<LI><SPAN style="COLOR: #0000ff">Create</SPAN> <SPAN style="COLOR: #0000ff">Unique</SPAN> <SPAN style="COLOR: #0000ff">Index</SPAN> idx_Tb_Hxl_Hash_mid_u1<BR></LI>
<LI><SPAN style="COLOR: #0000ff">On</SPAN> Tb_Hxl_Hash_mid<SPAN style="COLOR: #0000cc">(</SPAN>Id<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI><BR></LI>
<LI><SPAN style="COLOR: #0000ff">Create</SPAN> <SPAN style="COLOR: #0000ff">Unique</SPAN> <SPAN style="COLOR: #0000ff">Index</SPAN> idx_Tb_Hxl_Hash_u1 <SPAN style="COLOR: #0000ff">On</SPAN> Tb_Hxl_Hash<SPAN style="COLOR: #0000cc">(</SPAN>Id<SPAN style="COLOR: #0000cc">)</SPAN><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"><SPAN style="COLOR: #0000ff">Alter</SPAN> <SPAN style="COLOR: #0000ff">Table</SPAN> Tb_Hxl_Hash Exchange Partition p_h1 <BR></LI>
<LI><SPAN style="COLOR: #0000ff">With</SPAN> <SPAN style="COLOR: #0000ff">Table</SPAN> Tb_Hxl_Hash_mid <SPAN style="COLOR: #ff0000">Including</SPAN> Indexes<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><BR></LI>
<LI>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN style="COLOR: #0000cc">*</SPAN><BR></LI>
<LI>ERROR at line 2<SPAN style="COLOR: #0000cc">:</SPAN><BR></LI>
<LI>ORA<SPAN style="COLOR: #0000cc">-</SPAN>14098<SPAN style="COLOR: #0000cc">:</SPAN> <SPAN style="COLOR: #0000ff">index</SPAN> mismatch <SPAN style="COLOR: #0000ff">for</SPAN> tables <SPAN style="COLOR: #0000ff">in</SPAN> <SPAN style="COLOR: #0000ff">ALTER</SPAN> <SPAN style="COLOR: #0000ff">TABLE</SPAN> EXCHANGE PARTITION</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>Drop Index idx_Tb_Hxl_Hash_u1;</LI>
<LI></LI>
<LI>Create Unique Index idx_Tb_Hxl_Hash_u1 On Tb_Hxl_Hash(Id)</LI>
<LI></LI>
<LI>Local;</LI></OL></DIV>-- 交换前各自索引大小
<P>&nbsp;</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>SQL&gt; Select A.Bytes, a.segment_name</LI>
<LI></LI>
<LI>2 From Dba_Segments a</LI>
<LI></LI>
<LI>3 Where A.Segment_Name In ('IDX_TB_HXL_HASH__MID_U1')</LI>
<LI></LI>
<LI>4 Or (A.Segment_Name = 'IDX_TB_HXL_HASH_U1' And</LI>
<LI></LI>
<LI>5 A.Partition_Name = 'P_H1')</LI>
<LI></LI>
<LI>6 /</LI>
<LI></LI>
<LI></LI>
<LI></LI>
<LI>BYTES SEGMENT_NAME</LI>
<LI></LI>
<LI>------- ------------------------------</LI>
<LI></LI>
<LI>65536 IDX_TB_HXL_HASH__MID_U1</LI>
<LI></LI>
<LI>262144 IDX_TB_HXL_HASH_U1</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>Alter Table Tb_Hxl_Hash Exchange Partition p_h1 </LI>
<LI></LI>
<LI>With Table Tb_Hxl_Hash_mid Including Indexes;</LI></OL></DIV>
<P>--交换后各自索引的大小,从大小改变可以发现两个表之间的索引已经交换</P>
<P>&nbsp;</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>SQL&gt; Select A.Bytes, a.segment_name</LI>
<LI></LI>
<LI>2 From Dba_Segments a</LI>
<LI></LI>
<LI>3 Where A.Segment_Name In ('IDX_TB_HXL_HASH__MID_U1')</LI>
<LI></LI>
<LI>4 Or (A.Segment_Name = 'IDX_TB_HXL_HASH_U1' And</LI>
<LI></LI>
<LI>5 A.Partition_Name = 'P_H1')</LI>
<LI></LI>
<LI>6 /</LI>
<LI></LI>
<LI></LI>
<LI></LI>
<LI>BYTES SEGMENT_NAME</LI>
<LI></LI>
<LI>------- ------------------------------</LI>
<LI></LI>
<LI>262144 IDX_TB_HXL_HASH__MID_U1</LI>
<LI></LI>
<LI>65536 IDX_TB_HXL_HASH_U1</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_hxl_user_mid<BR></LI>
<LI>Exchange subpartition P_20110518_P_01 <BR></LI>
<LI><SPAN style="COLOR: #0000ff">With</SPAN> <SPAN style="COLOR: #0000ff">Table</SPAN> tb_hxl_user_mid_0518<SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV>
<P>&nbsp;</P>
<P>&nbsp;</P>
页: [1]
查看完整版本: Exchange交换分区索引