Oracle 分区表索引
SQL>create table part_tab1 (id int) partition by range (id)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (maxvalue)
) ;
Table created
SQL> insert into part_tab1 select rownum from dual connect by rownumcommit;
Commit complete
SQL> createindex>
Index created
查看索引状态，都是有效果的
SQL> select index_name,partition_name,status fromuser_ind_partitions where index_name='IDX_ID';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------------------------------------------
IDX_ID P1 USABLE
IDX_ID P2 USABLE
IDX_ID P3 USABLE
删除分区表分区1
SQL>>
Table truncated
SQL> select count(*) from part_tab1 partition(p1);
COUNT(*)
-------------------
0
查看索引并没有失效
SQL>select index_name,partition_name,status fromuser_ind_partitions where index_name='IDX_ID';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ------------------------------------------------
IDX_ID P1 USABLE
IDX_ID P2 USABLE
IDX_ID P3 USABLE
说明对于局部索引，直接TURNCATE分区是不会影响分区索引的！！！
再实验分区交换的情况，步骤如下：
SQL> create table normal_tab1(id int);
Table created
SQL> insert into normal_tab1 select rownum from dual connect by rownum commit;
Commit complete
SQL> select count(*) from normal_tab1;
COUNT(*)
----------------------
9
SQL> create index>
Index created
SQL>>
Table> COUNT(*)
----------------------
0
SQL> select count(*) from part_tab1 partition(p1);
COUNT(*)
--------------------
9
分区交换成功后，索引仍然有效！
SQL>select index_name,partition_name,status fromuser_ind_partitions where index_name='IDX_ID';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ -------------------------------------------------
IDX_ID P1 USABLE
IDX_ID P2 USABLE
IDX_ID P3 USABLE
由此也可见分区索引的交换加上关键字including indexes 后索引是生效的。
SQL>>
Table>
SQL>select index_name,partition_name,status fromuser_ind_partitions where index_name='IDX_ID';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------------------------------------------
IDX_ID P1 UNUSABLE
IDX_ID P2 USABLE
IDX_ID P3 USABLE
说明如果没有加including indexes进行分区交换，该索引立马失效。甚至导致原来的临时表的索引也失效了，也要重建
SQL> select index_name ,statusfrom user_indexes where index_name='IDX_NORM';
INDEX_NAME STATUS
------------------------------ -------------------------------------
IDX_NORM UNUSABLE
SQL>>
Index>
SQL>>
Index>
页:
[1]