环境准备

创建分区表

1. --创建表结构
2. CREATE TABLE sf2021
3. (
4. sf_id int NOT NULL,
5. sf_name varchar2(40) NOT NULL,
6. sf_address VARCHAR2(20) NOT NULL,
7. insert_time DATE DEFAULT SYSDATE NOT NULL
8. )
9. PARTITION BY RANGE (insert_time)
10. (
11. PARTITION p202102 VALUES LESS THAN (to_date('202102','yyyymm')),
12. PARTITION p202103 VALUES LESS THAN (to_date('202103','yyyymm')),
13. PARTITION p202104 VALUES LESS THAN (to_date('202104','yyyymm')),
14. PARTITION p202105 VALUES LESS THAN (to_date('202105','yyyymm')),
15. PARTITION p202106 VALUES LESS THAN (to_date('202106','yyyymm')),
16. PARTITION p202107 VALUES LESS THAN (to_date('202107','yyyymm')),
17. PARTITION p202108 VALUES LESS THAN (to_date('202108','yyyymm')),
18. PARTITION pmax VALUES LESS THAN (maxvalue));
20. --创建插入程序
21. create or replace procedure proc_sf2021_insert(tablecount int)
22. as
23. begin
24. for i in 1..tablecount loop
25. insert into mytest.sf2021
26. select
27. trunc(DBMS_RANDOM.VALUE(100000,10000000000)),
28. 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','爱国','中华'),
29. '山东省'|| DECODE(DBMS_RANDOM.STRING(0, 1),'A','济南市','B','德州市','C','青岛市','D','潍坊市','E','淄博市','F','滨州市','G','临沂市','H','烟台市','L','日照市','M','东营市','N','菏泽市','聊城市'),
30. to_date('2021/'||trunc(dbms_random.value(1,8))||'/'||trunc(dbms_random.value(1,28)),'yyyy/mm/dd') from dual;
31. end loop;
32. commit;
33. end;
34. /
36. --插入一万条数据
37. exec proc_sf2021_insert(10000);
39. --查看分区分布
40. col insert_time for a20
41. select to_char(insert_time,'yyyy/mm') insert_time,count(*)
42. from sf2021 group by to_char(insert_time,'yyyy/mm');

本地分区索引

本地分区索引，只适用于分区表分区键

1. --分区键，创建本地分区索引
2. create index sf2021_idx_id on sf2021(insert_time) local;
4. --查看分区状态 --索引及分区类型可参考 dba_part_indexes
5. SQL> select index_name,PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where index_name='SF2021_IDX_ID';
7. INDEX_NAME PARTITION_NAME STATUS
8. ------------------------------ ------------------------------ --------
9. SF2021_IDX_ID P202102 USABLE
10. SF2021_IDX_ID P202103 USABLE
11. SF2021_IDX_ID P202104 USABLE
12. SF2021_IDX_ID P202105 USABLE
13. SF2021_IDX_ID P202106 USABLE
14. SF2021_IDX_ID P202107 USABLE
15. SF2021_IDX_ID P202108 USABLE
16. SF2021_IDX_ID PMAX USABLE
18. 8 rows selected.
19. --创建全局hash分区
20. create index sf2021_idx_hash on sf2021(sf_id) global partition by hash(sf_id) partitions 6;

全局分区索引

全局分区索引 可分为范围和散列.

1. --创建全局hash分区
2. create index sf2021_idx_hash on sf2021(sf_id) global partition by hash(sf_id) partitions 6;
4. --查看
5. SQL> select index_name,PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where index_name='SF2021_IDX_HASH';
7. INDEX_NAME PARTITION_NAME STATUS
8. ------------------------------ ------------------------------ --------
9. SF2021_IDX_HASH SYS_P61 USABLE
10. SF2021_IDX_HASH SYS_P62 USABLE
11. SF2021_IDX_HASH SYS_P63 USABLE
12. SF2021_IDX_HASH SYS_P64 USABLE
13. SF2021_IDX_HASH SYS_P65 USABLE
14. SF2021_IDX_HASH SYS_P66 USABLE
16. 6 rows selected.

注意，全局分区索引，在维护分区时，可能造成索引无法使用，需要检查分区索引状态及重建无效索引.

1. alter index sf2021_idx_hash rebuild partition sys_p66;

前缀分前缀索引

1. --在本地分区创建唯一索引时，索引必须包括分区列,例如
2. create unique index sf2021_idx_pk on sf2021(sf_id,insert_time) local;
4. --当需要定义主键约束时，首先创建一个唯一索引，再添加约束（分步执行，可以单独管理），如
5. create unique index sf2021_idx_pk on sf2021(sf_id,insert_time) local;
6. alter table sf2021 add constraint sf2021_idx_pk primary key(sf_id,insert_time);

维护分区表及索引

1. --添加分区
2. alter table sf2021 add partition p202109 values less than (to_date('202108','yyyymm')) tablespace users;
4. --截断分区
5. alter table sf2021 truncate partition p202101;
7. --删除分区
8. alter table sf2021 drop partition p202101;
10. --移动分区
11. alter table sf2021 move partition p202106 tablespace mytest;
13. --拆分分区
14. alter table sf2021 split partition pmax at('2021/09') into (partition p202109 tablespace mytest,
15. partition pmax tablespace mytest);
17. --合并分区
18. alter table sf2021 merge partitions p202101,pmax into partition pmax;
20. --查看分区相关索引情况
21. set lines 200 pages 999
22. select index_name,null partition_name,status from dba_indexes where table_name='SF2021' and partitioned='NO'
23. union all
24. select index_name,partition_name,status from dba_ind_partitions
25. where index_name in (select index_name from dba_indexes where table_name='SF2021') order by 1,2,3;

表级分区操作对分区索引的影响