3.3.3 更多的分区访问类型

参考:Oracle分区执行计划

range分区可用的操作

分区表，按 n1 ,n2 分区

• partition range single:访问单个分区

• partition range iterator:访问多个分区

• partition range inlist: 分区键中用了in 例如： where n1 in(X1,X2) and n2=X3

• partition range all: 所有的分区

• partition range empty: 条件在分区中不存在 （或者说是找不到数据）

• partition range or: 分区键中用了or 例如 where n1=X1 or n2=X2

• partition range subquery:

• partition range join-filter:

• partition range multi-column:

hash分区可用的操作

• partition hash single:

• partition hash iterator:

• partition hash inlist

• partition hash all

• partition hash subquery

• partition hash join-filter

比range少了partition range or和partition range multi-column

list分区可用的操作

• partition list single

• partition list iterator

• partition list inlist

• partition list all

• partition list empty

• partition list or

• partition list subquery

• partition list join-filter

--1 PARTITION LIST SINGLE
EXPLAIN PLAN FOR select * from MY_TABLE partition(month_part_202107);
--2 PARTITION LIST SINGLE
EXPLAIN PLAN FOR select * from MY_TABLE where month_part='202107';
--3 PARTITION LIST ALL
EXPLAIN PLAN FOR select * from MY_TABLE where month_part=202107;
--4 PARTITION LIST ALL
EXPLAIN PLAN FOR select * from MY_TABLE where month_id ='202107';
--5 PARTITION LIST ALL
EXPLAIN PLAN FOR select * from MY_TABLE where month_id =202107;

---1、partition list inlist 不连续分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_part in ('202107','202108');
---2、partition list inlist 不连续分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_part='202107' or month_part='202108';
--3、partition list iterator 连续分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_part<='202107' ;
--4、partition list single 单个分区扫描
EXPLAIN PLAN FOR 
select * from MY_TABLE 
where month_part='202107' 
union all
select * from MY_TABLE 
where month_part='202108' ;
--5、partition list full 全部分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_id in ('202107','202108');
​