【INDEX】Oracle分割槽索引技術詳解

環境準備

建立分割槽表

--建立表結構CREATE TABLE sf2021(  sf_id              int        NOT NULL,  sf_name             varchar2(40)           NOT NULL,  sf_address             VARCHAR2(20)       NOT NULL,  insert_time            DATE                    DEFAULT SYSDATE NOT NULL  )PARTITION BY RANGE (insert_time)( PARTITION p202102 VALUES LESS THAN (to_date('202102','yyyymm')), PARTITION p202103 VALUES LESS THAN (to_date('202103','yyyymm')), PARTITION p202104 VALUES LESS THAN (to_date('202104','yyyymm')), PARTITION p202105 VALUES LESS THAN (to_date('202105','yyyymm')), PARTITION p202106 VALUES LESS THAN (to_date('202106','yyyymm')), PARTITION p202107 VALUES LESS THAN (to_date('202107','yyyymm')), PARTITION p202108 VALUES LESS THAN (to_date('202108','yyyymm')), PARTITION pmax VALUES LESS THAN (maxvalue)); --建立插入程式create or replace procedure proc_sf2021_insert(tablecount int)asbegin  for i in 1..tablecount loop   insert into mytest.sf2021select trunc(DBMS_RANDOM.VALUE(100000,10000000000)),DECODE(DBMS_RANDOM.STRING(0, 1),'A','張','B','周','C','李','D','趙','E','安','F','王小','G','楊','楊') || DECODE(DBMS_RANDOM.STRING(0, 1),'A','娜','B','娟','C','梅','D','美','E','鵬','F','愛國','中華'),'山東省'|| DECODE(DBMS_RANDOM.STRING(0, 1),'A','濟南市','B','德州市','C','青島市','D','濰坊市','E','淄博市','F','濱州市','G','臨沂市','H','煙臺市','L','日照市','M','東營市','N','菏澤市','聊城市'),to_date('2021/'||trunc(dbms_random.value(1,8))||'/'||trunc(dbms_random.value(1,28)),'yyyy/mm/dd') from dual;  end loop;  commit;end;/--插入一萬條資料exec proc_sf2021_insert(10000);--檢視分割槽分佈col insert_time for a20select to_char(insert_time,'yyyy/mm') insert_time,count(*) from sf2021 group by to_char(insert_time,'yyyy/mm');

本地分割槽索引

本地分割槽索引，只適用於分割槽表分割槽鍵

--分割槽鍵，建立本地分割槽索引create index sf2021_idx_id on sf2021(insert_time) local;--檢視分割槽狀態   --索引及分割槽型別可參考 dba_part_indexesSQL> select index_name,PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where index_name='SF2021_IDX_ID';INDEX_NAME               PARTITION_NAME              STATUS------------------------------ ------------------------------ --------SF2021_IDX_ID               P202102                  USABLESF2021_IDX_ID               P202103                  USABLESF2021_IDX_ID               P202104                  USABLESF2021_IDX_ID               P202105                  USABLESF2021_IDX_ID               P202106                  USABLESF2021_IDX_ID               P202107                  USABLESF2021_IDX_ID               P202108                  USABLESF2021_IDX_ID               PMAX                  USABLE8 rows selected.--建立全域性hash分割槽create index sf2021_idx_hash on sf2021(sf_id) global partition by hash(sf_id) partitions 6;

全域性分割槽索引

全域性分割槽索引 可分為範圍和雜湊.

--建立全域性hash分割槽create index sf2021_idx_hash on sf2021(sf_id) global partition by hash(sf_id) partitions 6;--檢視SQL> select index_name,PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where index_name='SF2021_IDX_HASH';INDEX_NAME               PARTITION_NAME              STATUS------------------------------ ------------------------------ --------SF2021_IDX_HASH            SYS_P61                  USABLESF2021_IDX_HASH            SYS_P62                  USABLESF2021_IDX_HASH            SYS_P63                  USABLESF2021_IDX_HASH            SYS_P64                  USABLESF2021_IDX_HASH            SYS_P65                  USABLESF2021_IDX_HASH            SYS_P66                  USABLE6 rows selected.

注意，全域性分割槽索引，在維護分割槽時，可能造成索引無法使用，需要檢查分割槽索引狀態及重建無效索引.

alter index sf2021_idx_hash rebuild partition sys_p66;

字首分字首索引

--在本地分割槽建立唯一索引時，索引必須包括分割槽列,例如create unique index sf2021_idx_pk on sf2021(sf_id,insert_time) local;--當需要定義主鍵約束時，首先建立一個唯一索引，再新增約束（分步執行，可以單獨管理），如create unique index sf2021_idx_pk on sf2021(sf_id,insert_time) local;alter table sf2021 add constraint sf2021_idx_pk primary key(sf_id,insert_time);

維護分割槽表及索引

--新增分割槽alter table sf2021 add partition p202109 values less than (to_date('202108','yyyymm')) tablespace users;--截斷分割槽alter table sf2021 truncate partition p202101;--刪除分割槽alter table sf2021 drop partition p202101;--移動分割槽alter table sf2021 move partition p202106 tablespace mytest;--拆分分割槽alter table sf2021 split partition pmax at('2021/09') into (partition p202109 tablespace mytest,partition pmax tablespace mytest);--合併分割槽alter table sf2021 merge partitions p202101,pmax into partition pmax;--檢視分割槽相關索引情況set lines 200 pages 999select index_name,null partition_name,status from dba_indexes where table_name='SF2021' and partitioned='NO'union allselect index_name,partition_name,status from dba_ind_partitionswhere index_name in (select index_name from dba_indexes where table_name='SF2021') order by 1,2,3;

表級分割槽操作對分割槽索引的影響