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') ;