分区表相关

hxl 发表于 2011-12-22 08:54

<DIV>1.hash分区表</DIV>
<DIV>哈希分区表上的全局索引和本地索引，在新增分区后都会失效，需要重建索引.</DIV>
<DIV>-- 1.1创建哈希分区表</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>Create Table tb_hxl_hash </LI>
<LI> ( </LI>
<LI> Id Number Not Null, </LI>
<LI> cnt Number </LI>
<LI> ) </LI>
<LI>Partition By Hash(Id) </LI>
<LI>( </LI>
<LI>Partition p_hs1, </LI>
<LI>Partition p_hs2, </LI>
<LI>Partition p_hs3, </LI>
<LI>Partition p_hs4 </LI>
<LI>); </LI>
<LI></LI></OL></DIV></DIV>
<DIV>-- 1.2 创建全局索引</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>Create Index idx_tb_hxl_hash_n1 On tb_hxl_hash(Id) Global; </LI>
<LI></LI></OL></DIV></DIV>
<DIV>-- 1.3 生成数据</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>Declare </LI>
<LI> Begin </LI>
<LI>    For i In 1 .. 100000 Loop </LI>
<LI>    Insert Into Tb_Hxl_Hash Values (i, i); </LI>
<LI>    End Loop; </LI>
<LI>  Commit; </LI>
<LI>  End; </LI>
<LI></LI></OL></DIV></DIV>
<DIV>-- 1.4 这个时候查询索引状态为有效</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>Select status </LI>
<LI> From user_indexes </LI>
<LI>Where INDEX_NAME = 'IDX_TB_HXL_HASH_N1'; </LI>
<LI></LI>
<LI>STATUS </LI>
<LI>-------- </LI>
<LI> </LI>
<LI>VALID</LI></OL></DIV></DIV>
<DIV>-- 1.5 新增分区 </DIV>
<DIV>hash分区表新增分区后全局索引会失效</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>Alter Table tb_hxl_hash Add Partition p_hs5; </LI>
<LI></LI></OL></DIV></DIV>
<DIV> </DIV>
<DIV>-- 1.6 这个时候查询索引已经失效</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>Select status </LI>
<LI> From user_indexes </LI>
<LI> Where INDEX_NAME = 'IDX_TB_HXL_HASH_N1'; </LI>
<LI></LI>
<LI>STATUS </LI>
<LI>-------- </LI>
<LI> </LI>
<LI>UNUSABLE </LI>
<LI></LI></OL></DIV></DIV>
-- 1.7 创建本地索引
<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_n1; </LI>
<LI>Create Index idx_tb_hxl_hash_n1 On tb_hxl_hash(Id) Local;</LI></OL></DIV>
-- 1.8 索引状态这个时候是有效的
<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>Select aa.partition_name,status </LI>
<LI>  From user_ind_partitions aa </LI>
<LI> Where index_name = 'IDX_TB_HXL_HASH_N1'; </LI>
<LI> </LI>
<LI>PARTITION_NAME STATUS </LI>
<LI>------------------------------ -------- </LI>
<LI> </LI>
<LI>P_HS1 USABLE </LI>
<LI>P_HS2 USABLE </LI>
<LI>P_HS3 USABLE </LI>
<LI>P_HS4 USABLE </LI>
<LI>P_HS5 USABLE</LI></OL></DIV>
-- 1.9 新增分区
<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 Add Partition p_hs6;</LI></OL></DIV>
-- 1.10 新增分区后部分索引已经失效
<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>Select aa.partition_name,status </LI>
<LI>  From user_ind_partitions aa </LI>
<LI> Where index_name = 'IDX_TB_HXL_HASH_N1'; </LI>
<LI> </LI>
<LI>PARTITION_NAME STATUS </LI>
<LI>------------------------------ -------- </LI>
<LI> </LI>
<LI>P_HS1 UNUSABLE </LI>
<LI>P_HS2 USABLE </LI>
<LI>P_HS3 USABLE </LI>
<LI>P_HS4 USABLE </LI>
<LI>P_HS5 USABLE </LI>
<LI>P_HS6 UNUSABLE</LI></OL></DIV>
--　1.11 删除hash分区
<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> Alter Table hxl.tb_hxl_hash Drop Partition P_HS5; </LI>
<LI>Alter Table hxl.tb_hxl_hash Drop Partition P_HS5 </LI>
<LI>                                           * </LI>
<LI>第 1 行出现错误: </LI>
<LI>ORA-14255: 未按范围, 列表, 组合范围或组合列表方法对表进行分区</LI></OL></DIV>
说明hash分区表是不能删除分区的.
-- 1.11 合并分区
hash分区的表不能单独删除某个分区,但可以合并分区
<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 hxl.tb_hxl_hash </LI>
<LI>     COALESCE Partition;</LI></OL></DIV>
该命令执行后,最后一个分区的数据合并到其他分区,同时删除该分区.合并分区后不管是本地索引还是全局索引都会失效(UNUSABLE).
-- 1.12 交换分区
<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_HS2 </LI>
<LI>With Table tb_hxl_ex;</LI></OL></DIV>
交换分区后分区表的分区索引和交换表的索引会失效. 在该例子中p_hs2对应的分区索引和交换表tb_hxl_ex的索引会失效.
2.List分区表
2.1创建表并生成数据
<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>Create Table hxl.tb_hxl_list </LI>
<LI>( </LI>
<LI>  Id Number, </LI>
<LI>  provcode Number </LI>
<LI>) </LI>
<LI>Partition By List(provcode) </LI>
<LI>( </LI>
<LI>  Partition p_l1 Values(0), </LI>
<LI>  Partition p_l2 Values(1), </LI>
<LI>  Partition p_l3 Values(2), </LI>
<LI>  Partition p_l4 Values(3) </LI>
<LI>); </LI>
<LI> </LI>
<LI> </LI>
<LI>Declare </LI>
<LI>Begin </LI>
<LI>    For i In 1 .. 100000 Loop </LI>
<LI>       Insert Into hxl.tb_hxl_list Values(i,round(dbms_random.value(0,3))); </LI>
<LI>      End Loop; </LI>
<LI>     Commit; </LI>
<LI>  End;</LI></OL></DIV>
2.2 在非分区键上创建全局索引
<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>Create Index hxl.IDX_TB_HXL_LIST_N1 </LI>
<LI>On hxl.tb_hxl_list(Id) Global;</LI></OL></DIV>
2.3 增加分区
<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 hxl.tb_hxl_list </LI>
<LI>  Add Partition p_l5 Values(4);</LI></OL></DIV>
新增list分区索引不会失效
<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> Select status,INDEX_NAME </LI>
<LI>  2 From dba_indexes a </LI>
<LI>  3 Where INDEX_NAME = 'IDX_TB_HXL_LIST_N1'; </LI>
<LI> </LI>
<LI>STATUS INDEX_NAME </LI>
<LI>-------- ------------------------------ </LI>
<LI> </LI>
<LI>VALID IDX_TB_HXL_LIST_N1</LI></OL></DIV>
2.4 删除list分区
先向新分区写入数据
<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>Insert Into hxl.tb_hxl_list Values(100,4); </LI>
<LI>Commit;</LI></OL></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>SQL> Select Count(1) </LI>
<LI>  2 From hxl.tb_hxl_list Partition (p_l5); </LI>
<LI> </LI>
<LI>  COUNT(1) </LI>
<LI>---------- </LI>
<LI> </LI>
<LI>         1</LI></OL></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>Alter Table hxl.tb_hxl_list </LI>
<LI>  Drop Partition p_l5 ;</LI></OL></DIV>
删除list分区,索引失效
<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> Select status,index_name </LI>
<LI>  2 From dba_indexes a </LI>
<LI>  3 Where a.index_name = 'IDX_TB_HXL_LIST_N1'; </LI>
<LI> </LI>
<LI>STATUS INDEX_NAME </LI>
<LI>-------- ------------------------------ </LI>
<LI> </LI>
<LI>UNUSABLE IDX_TB_HXL_LIST_N1</LI></OL></DIV>
2.5 创建本地索引
<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 hxl.IDX_TB_HXL_LIST_N1; </LI>
<LI>Create Index hxl.IDX_TB_HXL_LIST_N1 </LI>
<LI>On hxl.tb_hxl_list(Id) Local;</LI></OL></DIV>
2.6 新增分区
<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 hxl.tb_hxl_list </LI>
<LI>  Add Partition p_l5 Values(4);</LI></OL></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>SQL> Select index_name,aa.partition_name,status </LI>
<LI>  2 From dba_ind_partitions aa </LI>
<LI>  3 Where index_name In( 'IDX_TB_HXL_LIST_N1'); </LI>
<LI> </LI>
<LI>INDEX_NAME PARTITION_NAME STATUS </LI>
<LI>------------------------------ ------------------------------ -------- </LI>
<LI> </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L1 USABLE </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L2 USABLE </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L3 USABLE </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L4 USABLE </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L5 USABLE</LI></OL></DIV>
2.7 删除分区
先向新增的分区中写入数据
<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>Insert Into hxl.tb_hxl_list Values(100,4); </LI>
<LI>Commit;</LI></OL></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>SQL> Select Count(1) </LI>
<LI>  2 From hxl.tb_hxl_list Partition (p_l5); </LI>
<LI> </LI>
<LI>  COUNT(1) </LI>
<LI>---------- </LI>
<LI> </LI>
<LI>         1</LI></OL></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>Alter Table hxl.tb_hxl_list </LI>
<LI>  Drop Partition p_l5 ;</LI></OL></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>SQL> Select index_name,aa.partition_name,status </LI>
<LI>  2 From dba_ind_partitions aa </LI>
<LI>  3 Where index_name In( 'IDX_TB_HXL_LIST_N1'); </LI>
<LI> </LI>
<LI>INDEX_NAME PARTITION_NAME STATUS </LI>
<LI>------------------------------ ------------------------------ -------- </LI>
<LI> </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L1 USABLE </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L2 USABLE </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L3 USABLE </LI>
<LI>IDX_TB_HXL_LIST_N1 P_L4 USABLE</LI></OL></DIV>
测试说明list分区表,删除某个分区后，全局索引会失效，而本地索引则不会失效。

页: [1]

Chinaunix's Archiver

分区表相关