Oracle drop,truncate partition 索引失效 实验

--Oracle drop,truncate partition 索引失效实验
 
create tablespace part_tbs1 datafile '/oradata/dg1/part_tbs1.dbf' size 128m;
create tablespace part_tbs2 datafile '/oradata/dg1/part_tbs2.dbf' size 128m;
create tablespace part_tbs3 datafile '/oradata/dg1/part_tbs3.dbf' size 128m;
create tablespace part_tbs4 datafile '/oradata/dg1/part_tbs4.dbf' size 128m;


alter user scott quota unlimited on part_tbs1 ;
alter user scott quota unlimited on part_tbs2 ;
alter user scott quota unlimited on part_tbs3 ;
alter user scott quota unlimited on part_tbs4 ;


create table tabpart (id integer)
 partition by range(id)
 (
 PARTITION part_01 VALUES less than(10) tablespace part_tbs1,
 partition part_02 values less than(20) tablespace part_tbs2,
 partition part_03 values less than(30) tablespace part_tbs3,
 partition part_04 values less than(40) tablespace part_tbs4
 ); 
 
insert into tabpart values(1);
insert into tabpart values(11);
insert into tabpart values(21);
insert into tabpart values(31); 
commit;


--查看索引语句 
set long 999999
select dbms_metadata.get_ddl('INDEX','INDEX_LOC','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_GLOBAL','SCOTT') from dual;


--创建全局索引
create index index_global on tabpart (id) global;
select status ,index_name from user_indexes where index_name = 'INDEX_GLOBAL';






--Truncate partition 
SQL> alter table tabpart truncate partition part_01;


--索引失效,当truncate一个分区时，全局索引会失效。
SQL> select status ,index_name from user_indexes where index_name = 'INDEX_GLOBAL';
STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_GLOBAL 


SQL> select count(*) from tabpart partition(part_01);
  COUNT(*)
----------
         0
--Truncate partition时，更新全局索引，不会失效 
SQL> alter table tabpart truncate partition part_03 update global indexes;


Table truncated.


SQL> select status ,index_name from user_indexes where index_name = 'INDEX_GLOBAL';


STATUS   INDEX_NAME
-------- ---------------
VALID    INDEX_GLOBAL


--Truncate partition时，更新所有索引，不会失效 
SQL> alter table tabpart truncate partition part_04 update indexes;


Table truncated.


SQL> select index_name,status,PARTITIONED,to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') , INDEX_TYPE,TABLE_NAME,TABLE_type 
  from user_indexes where table_name = 'TABPART';
  
INDEX_NAME      STATUS   PAR TO_CHAR(LAST_ANAL INDEX_TYPE                  TABLE_NAME                     TABLE_TYPE
--------------- -------- --- ----------------- --------------------------- ------------------------------ -----------
INDEX_GLC       VALID    NO  20150415 13:28:46 NORMAL                      TABPART                        TABLE
INDEX_LOC       N/A      YES 20150415 13:10:02 NORMAL                      TABPART                        TABLE
INDEX_GLOBAL    VALID    NO  20150415 13:10:02 NORMAL                      TABPART                        TABLE




--插入数据 part_01
SQL> insert into tabpart values(2);
1 row created.
SQL> commit;
Commit complete.


SQL> alter table tabpart drop partition part_02;
Table altered.


SQL>  select statusselect dbms_metadata.get_ddl('INDEX','INDEX_GLC','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX','INDEX_GLC','SCOTT')  ,index_name from user_indexes where index_name = 'INDEX_GLOBAL';
STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_GLOBAL


--重建索引 rebuild
对于大表的索引可以使用并行和nologging
alter index index_global parallel 8 nologging ;


SQL> alter index INDEX_GLOBAL rebuild;
Index altered.


SQL> select index_name,status,PARTITIONED,to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') , INDEX_TYPE,TABLE_NAME,TABLE_type 
 from user_indexes where index_name = 'INDEX_GLOBAL';
INDEX_NAME      STATUS   PAR TO_CHAR(LAST_ANAL INDEX_TYPE                  TABLE_NAME                     TABLE_TYPE
--------------- -------- --- ----------------- --------------------------- ------------------------------ -----------
INDEX_GLOBAL    VALID    NO  20150415 13:10:02 NORMAL                      TABPART                        TABLE


全局索引在drop partition、truncate partition后索引都会失效,对于Global index，
Oracle提供了一参数update global indexes，可避免truncate或drop partition时索引失效问题，
另外一种方法是 rebuild。


--增加列 
SQL> alter table tabpart add (name varchar2(100));
Table altered.


--创建全局索引
SQL> create index index_loc on tabpart(name) local;
Index created.
SQL> update tabpart set name='zhangsan' where id=2;
1 row updated.
SQL> update tabpart set name='lisi' where id=21;
1 row updated.
SQL> update tabpart set name='wangwu' where id=31;
1 row updated.
SQL> commit;
Commit complete.


SQL> col name for a40
SQL> set lines 100
SQL> select * from tabpart;
        ID NAME
---------- ----------------------------------------
         2 zhangsan
        21 lisi
        31 wangwu
SQL> select status ,index_name from user_indexes where table_name='TABPART';
STATUS   INDEX_NAME
-------- ------------------------------
N/A      INDEX_LOC
VALID    INDEX_GLOBAL


col HIGH_VALUE for a10 
col HIGH_VALUE_LENGTH for 99 
col index_name for a15
col partition_name for a15
SQL> select index_name,partition_name,HIGH_VALUE,HIGH_VALUE_LENGTH,PARTITION_POSITION,status,TABLESPACE_NAME 
 from user_ind_partitions;
INDEX_NAME      PARTITION_NAME  HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION STATUS   TABLESPACE_NAME
--------------- --------------- ---------- ----------------- ------------------ -------- ------------------------------
INDEX_LOC       PART_01         10                         2                  1 USABLE   PART_TBS1
INDEX_LOC       PART_03         30                         2                  2 USABLE   PART_TBS3
INDEX_LOC       PART_04         40                         2                  3 USABLE   PART_TBS4


--rebuild 分区表分区索引 
SQL> ALTER INDEX index_loc  REBUILD PARTITION part_01;
Index altered. 
SQL> ALTER INDEX index_loc  REBUILD PARTITION part_03;
Index altered.
SQL> ALTER INDEX index_loc  REBUILD PARTITION part_04;
Index altered


--统计信息收集
SQL> ANALYZE TABLE tabpart  COMPUTE STATISTICS;


SQL> EXEC dbms_stats.gather_table_stats(ownname =>'SCOTT' ,tabname =>'TABPART' ,cascade => TRUE );


--查询分区表索引所在的分区
SQL> SELECT PI.TABLE_NAME,
       IP.INDEX_NAME,
       IP.PARTITION_NAME,
       IP.STATUS,
       IP.GLOBAL_STATS
  FROM USER_PART_INDEXES PI, USER_IND_PARTITIONS IP
 WHERE PI.INDEX_NAME = IP.INDEX_NAME
   AND PI.TABLE_NAME = 'TABPART';
 
TABLE_NAME                     INDEX_NAME                     PARTITION_NAME                 STATUS   GLO
------------------------------ ------------------------------ ------------------------------ -------- ---
TABPART                        INDEX_LOC                      PART_01                        USABLE   YES
TABPART                        INDEX_LOC                      PART_03                        USABLE   YES
TABPART                        INDEX_LOC                      PART_04                        USABLE   YES
 
--默认是普通索引，索引定义语句不带 global 
SQL> create index index_glc on tabpart (id,name); 
Index created.
SQL> select dbms_metadata.get_ddl('INDEX','INDEX_GLC','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX','INDEX_GLC','SCOTT') 
--------------------------------------------------------------------------------
  CREATE INDEX "SCOTT"."INDEX_GLC" ON "SCOTT"."TABPART" ("ID", "NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


--user_indexes PARTITIONED 为 yes 的索引是分区索引 
select index_name,status,PARTITIONED,to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') , INDEX_TYPE,TABLE_NAME,TABLE_type 
 from user_indexes where table_name='TABPART';
 
INDEX_NAME      STATUS   PAR TO_CHAR(LAST_ANAL INDEX_TYPE                  TABLE_NAME                     TABLE_TYPE
--------------- -------- --- ----------------- --------------------------- ------------------------------ -----------
INDEX_GLC       VALID    NO  20150415 13:28:46 NORMAL                      TABPART                        TABLE
INDEX_LOC       N/A      YES 20150415 13:10:02 NORMAL                      TABPART                        TABLE
INDEX_GLOBAL    VALID    NO  20150415 13:10:02 NORMAL                      TABPART                        TABLE




--重建索引 rebuild
对于大表的索引可以使用并行和 nologging
alter index index_global parallel 8 nologging ;


SQL> select dbms_metadata.get_ddl('INDEX','INDEX_GLOBAL','SCOTT') from dual;


DBMS_METADATA.GET_DDL('INDEX','INDEX_GLOBAL','SCOTT')
--------------------------------------------------------------------------------


  CREATE INDEX "SCOTT"."INDEX_GLOBAL" ON "SCOTT"."TABPART" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"




SQL> select index_name,locality from user_part_indexes;


INDEX_NAME                     LOCALI
------------------------------ ------
INDEX_LOC                      LOCAL




--增加列 
SQL> alter table tabpart add (id2 int);
Table altered.


--创建全局的分区索引 
create index tabpart_global_1_idx
     on tabpart(id2)
      global partition by range (id2)
      (partition GLOBAL1 values less than (10),
        partition GLOBAL2 values less than (20),
        partition GLOBAL3 values less than (30),
        partition GLOBAL4 values less than (MAXVALUE)
      )
 ;


--查询分区索引 
SQL> select index_name,partitioning_type,PARTITION_COUNT,LOCALITY from user_part_indexes ;


INDEX_NAME                          PARTITION   PARTITION_COUNT  LOCALI
------------------------------ ---------       ---------------           ----------
TABPART_GLOBAL_1_IDX         RANGE                   4                     GLOBAL
INDEX_LOC                             RANGE                   3                      LOCAL






--分区最大值 
select table_name,partition_name,high_value 
from user_tab_partitions 
where table_name in ('STM03M00','STM02M00','STM04M00','STI02M00','STI03M00') ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22661144/viewspace-1578354/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22661144/viewspace-1578354/