分区pruning是分区里的重要，通过partition pruning可以只访问需要的分区，实现减少IO的目的，如果不能partition pruning,则可能全表（分区）访问，因为分区表一般很大，很显然IO很多，效率低，
当然，如果全表（分区）访问，如果有索引，也可能走索引提高效率的。
另外就算不能分区pruning,如果全局分区索引，如果走全局分区索引，也可能利用到索引分区裁剪。分区裁剪分为表分区裁剪、索引分区裁剪。
###
要能正常分区pruning,需要注意条件的写法，条件是指where条件，join on条件，注意如下要点：
1）分区列不能显式或隐式类型转换
2）分区列不能使用函数或算术等运算
3）不能用自定义函数和分区列比较，自定义函数是未知的结果，就算是等值，CBO没有做动态分区裁剪判断，会直接全分表访问。
19c的BUG，可以将自定义函数改为deterministic或打补丁,则使用动态分区裁剪。
4) 对于list和range分区，可以使用等值，in list，范围查询，包括like后通配实现分区裁剪
5）对于hash分区，只能用等值和in list,不能用于范围条件实现分区裁剪
从上面看出，分区裁剪和index range scan差不多用法。
ORACLE分区裁剪，分为静态裁剪和动态裁剪，如下：
1）静态裁剪：编译期确定访问的分区范围，pstart和pstop会显示分区编号，比如1-2,这种一般分区比较值是具体值或范围，不能对分区列进行运算或类型转换，
对于number类型分区列，可以用=,>,>=,<,<=,between,in list等运算，不能用like运算，因为会类型转换成varchar,
对于varchar的，除了上面的，like后通配的可以分区裁剪，前通配的不行。
静态分区裁剪其实和索引使用差不多方式。
2）动态裁剪：顾名思义，也就是运行期才确定访问的分区范围，这种pstart和pstop显示KEY,
比如下面的都会用动态分区裁剪：
分区键比较的是绑定变量，
分区比较条件是子查询，
join的条件是分区列，然后分区表是nested loops内表，这种就是每次循环迭代确定扫描的分区。
分区键与自定义函数比较，注意19c的BUG，将函数改为deterministic或打补丁。
什么时候可以用分区裁剪：
1）对于range,list分区，对分区列使用范围运算（比如>,<),LIKE（前通配，分区列类型是VARCHAR2，这个好像有限制），等值条件，IN LIST条件，
则优化器可以pruning。一般使用>,<,>=,<=,=,in的较好，like的好像要写全分区定义的值才行。
2）对于hash分区，必须对分区列使用=或in list运算才能分区pruning，范围不行，这个和hash运算原理有关。
3）对于组合分区，会按照1,2对分区和子分区分别使用pruning
静态分区裁剪的例子：
比如：表sales_range_hash按照s_saledate range分区，然后s_productid做subpartition分区：
CREATE TABLE sales_range_hash(
s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE (s_saledate)
SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8
(PARTITION sal99q1 VALUES LESS THAN
(TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
PARTITION sal99q2 VALUES LESS THAN
(TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
PARTITION sal99q3 VALUES LESS THAN
(TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
PARTITION sal99q4 VALUES LESS THAN
(TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));
|
set line 200 pagesize 999
set autotrace traceonly exp
SELECT * FROM sales_range_hash
WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))
AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid = 1200;
执行计划ID=1显示走PARTITION RANGE ITERATOR，访问3和4分区，ID=2显示PARTITION HASH SINGLE，访问子分区3,这就是静态分区裁剪：
比如：表sales_range_hash按照s_saledate range分区，然后s_productid做subpartition分区：
Execution Plan
----------------------------------------------------------
Plan hash value: 575662819
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 1 | 48 | 2 (0)| 00:00:01 | 3 | 4 |
| 2 | PARTITION HASH SINGLE | | 1 | 48 | 2 (0)| 00:00:01 | 3 | 3 |
|* 3 | TABLE ACCESS FULL | SALES_RANGE_HASH | 1 | 48 | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S_PRODUCTID"=1200 AND "S_SALEDATE"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
|
按照条件time_id = to_date('01-jan-2001', 'dd-mon-yyyy')，time_id是范围分区，所以静态分区，访问17分区：
select * from sh.sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
Execution Plan
----------------------------------------------------------
Plan hash value: 3733446669
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 766 | 22980 | 29 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 766 | 22980 | 29 (0)| 00:00:01 | 17 | 17 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 766 | 22980 | 29 (0)| 00:00:01 | 17 | 17 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | | | | | 17 | 17 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TIME_ID"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
|
动态分区裁剪例子：
1）使用绑定变量
绑定变量，只能运行期确定其值，所以是动态分区裁剪：
var a varchar2(100);
var b varchar2(100);
select * from sh.sales where time_id in (:a, :b);
Execution Plan
----------------------------------------------------------
Plan hash value: 3822291910
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1259 | 36511 | 190 (0)| 00:00:03 | | |
| 1 | INLIST ITERATOR | | | | | | | |
| 2 | PARTITION RANGE ITERATOR | | 1259 | 36511 | 190 (0)| 00:00:03 |KEY(I) |KEY(I) |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 1259 | 36511 | 190 (0)| 00:00:03 |KEY(I) |KEY(I) |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | | | | |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TIME_ID"=:A OR "TIME_ID"=:B)
|
2）分区列比较条件是子查询
子查询的结果也是运行期确定的
select * from sh.sales where time_id in (select trunc(sysdate-100,'dd') from dual);
Execution Plan
----------------------------------------------------------
Plan hash value: 3126106919
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 6 (17)| 00:00:01 | | |
| 1 | VIEW | VM_NWVW_2 | 1 | 87 | 6 (17)| 00:00:01 | | |
| 2 | HASH UNIQUE | | 1 | 29 | 6 (17)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 29 | 5 (0)| 00:00:01 | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 29 | 5 (0)| 00:00:01 | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 1 | 29 | 5 (0)| 00:00:01 | KEY | KEY |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | | | | | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("TIME_ID"=TRUNC(SYSDATE@!-100,'fmdd'))
|
3）nested loops内表，join列是分区列
select t.time_id, sum(s.amount_sold)
from sh.sales s, sh.times t
where s.time_id = t.time_id and t.fiscal_year = 2000 and t.fiscal_week_number = 3
group by t.time_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 4272487730
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 168 | 77 (2)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 6 | 168 | 77 (2)| 00:00:01 | | |
| 2 | NESTED LOOPS | | | | | | | |
| 3 | NESTED LOOPS | | 3683 | 100K| 76 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | TIMES | 6 | 90 | 18 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR | | | | | | KEY | KEY |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 7 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | | | | | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 629 | 8177 | 76 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000)
7 - access("S"."TIME_ID"="T"."TIME_ID")
|
下面研究下哪些操作不能分区pruning:
1)显式或隐式类型转换
对分区列显式类型转换导致分区pruning用不了：
SELECT SUM(quantity_sold)
FROM sh.sales
WHERE to_char(time_id,'yyyy-mm-dd') = '2001-1-1';
执行计划扫描索引分区：PARTITION RANGE ALL
Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 551 (6)| 00:00:07 | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | PARTITION RANGE ALL| | 9188 | 98K| 551 (6)| 00:00:07 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 9188 | 98K| 551 (6)| 00:00:07 | 1 | 28 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'yyyy-mm-dd')='2001-1-1')
表SALES列time_id是date类型，右侧是timestamp,则time_id隐式类型转换为timestamp,导致扫描所有分区：
SELECT SUM(quantity_sold)
FROM sh.sales
WHERE time_id = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');
Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 540 (5)| 00:00:07 | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | PARTITION RANGE ALL| | 9188 | 98K| 540 (5)| 00:00:07 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 9188 | 98K| 540 (5)| 00:00:07 | 1 | 28 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("TIME_ID")=TIMESTAMP' 2000-01-01 00:00:00.000000000')
2)不能对分区列进行函数或运算
SELECT * FROM sales_range_hash
WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))
AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid +1 = 1201;
这里hash分区键做了运算s_productid +1 = 1201，则访问1-8所有分区PARTITION HASH ALL。而不是PARTITION HASH SINGLE
Execution Plan
----------------------------------------------------------
Plan hash value: 2685214063
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 1 | 48 | 2 (0)| 00:00:01 | 3 | 4 |
| 2 | PARTITION HASH ALL | | 1 | 48 | 2 (0)| 00:00:01 | 1 | 8 |
|* 3 | TABLE ACCESS FULL | SALES_RANGE_HASH | 1 | 48 | 2 (0)| 00:00:01 | 17 | 32 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S_SALEDATE"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S_PRODUCTID"+1=1201)
3）自定义函数19C有BUG
CREATE OR REPLACE FUNCTION "GET_KEY" (d in varchar2)
return varchar2
as
begin
return d;
end;
/
分区表
CREATE TABLE "LIST_PART"
( "ID" NUMBER,
"EXT" NUMBER,
"PART" VARCHAR2(100)
)
PARTITION BY LIST ("PART")
(PARTITION "P1" VALUES ('00'),
PARTITION "P2" VALUES ('01')
);
insert into list_part select level,'0','00' from dual connect by level <=100000;
insert into list_part select level+100000,'1','01' from dual connect by level <=100000;
commit;
select * from list_part where part = get_key('00');
oracle 11g:
11g正常，PARTITION LIST SINGLE，然后是动态分区pruning
Elapsed: 00:00:00.93
Execution Plan
----------------------------------------------------------
Plan hash value: 1107000748
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2201 | 167K| 165 (21)| 00:00:02 | | |
| 1 | PARTITION LIST SINGLE| | 2201 | 167K| 165 (21)| 00:00:02 | KEY | KEY |
| 2 | TABLE ACCESS FULL | LIST_PART | 2201 | 167K| 165 (21)| 00:00:02 | KEY | KEY |
---------------------------------------------------------------------------------------------------
oracle 19c:
可以从执行计划看到是PARTITION LIST ALL，不是扫描一个分区：
select * from list_part where part = get_key('00');
99999 rows selected.
Elapsed: 00:00:03.19
Execution Plan
----------------------------------------------------------
Plan hash value: 877256947
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 549K| 5908K| 1362 (20)| 00:00:01 | | |
| 1 | PARTITION LIST ALL| | 549K| 5908K| 1362 (20)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL| LIST_PART | 549K| 5908K| 1362 (20)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART"="GET_KEY"('00'))
oracle 19c bug:
Partition Pruning Not Work For SQL With Predicate Of User Function After Upgrade To 19c (Doc ID 2717940.1)
可以打补丁或将自定义函数改为deterministic：
CREATE OR REPLACE FUNCTION "GET_KEY" (d in varchar2)
return varchar2
deterministic
as
begin
return d;
end;
/
Elapsed: 00:00:00.67
现在正常，Pstart=KEY,是动态分区pruning，扫描一个分区PARTITION LIST SINGLE:
Execution Plan
----------------------------------------------------------
Plan hash value: 1107000748
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 549K| 5908K| 682 (20)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 549K| 5908K| 682 (20)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS FULL | LIST_PART | 549K| 5908K| 682 (20)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6913 consistent gets
0 physical reads
0 redo size
2262829 bytes sent via SQL*Net to client
73738 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99999 rows processed
4）LIKE分区裁剪
like要能裁剪，必须分区键是VARCHAR类型：
CREATE TABLE "LIST_PART1"
( "ID" NUMBER,
"EXT" NUMBER,
"PART" VARCHAR2(100)
)
PARTITION BY LIST ("PART")
(PARTITION "P1" VALUES ('00'),
PARTITION "P2" VALUES ('01'),
PARTITION "P3" VALUES ('10'),
PARTITION "P4" VALUES ('11'),
PARTITION "P5" VALUES ('20'),
PARTITION "P6" VALUES ('21')
);
对于list分区，可以裁剪：
select * from list_part1 where part like '0%';
Execution Plan
----------------------------------------------------------
Plan hash value: 1167254674
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST ITERATOR| | 1 | 78 | 2 (0)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | LIST_PART1 | 1 | 78 | 2 (0)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART" LIKE '0%')
##range分区
CREATE TABLE "RANGE_PART1"
( "ID" NUMBER,
"EXT" NUMBER,
"PART" VARCHAR2(100)
)
PARTITION BY RANGE ("PART")
(PARTITION "P1" VALUES LESS THAN ('00'),
PARTITION "P2" VALUES LESS THAN ('01'),
PARTITION "P3" VALUES LESS THAN ('10'),
PARTITION "P4" VALUES LESS THAN ('11'),
PARTITION "P5" VALUES LESS THAN ('20'),
PARTITION "P6" VALUES LESS THAN ('21')
);
insert into range_part1 select level,'0','10' from dual connect by level<10000;
commit;
range的like也能裁剪，不过要多扫描一个分区，当然，实际不扫描数据,比如下面的扫描1-3分区，但是第3个分区有数据，其实是作为like判断，不实际扫描3分区，
这就和索引range scan一样，端点非等值的只是作为范围判断，like实际会转为> and <：
select * from range_part1 where part like '0%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3339778082
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 1 | 78 | 2 (0)| 00:00:01 | 1 | 3 |
|* 2 | TABLE ACCESS FULL | RANGE_PART1 | 1 | 78 | 2 (0)| 00:00:01 | 1 | 3 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART" LIKE '0%')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
493 bytes sent via SQL*Net to client
397 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
##和索引一样，like前通配不能pruning：
select * from range_part1 where part like '%0';
Execution Plan
----------------------------------------------------------
Plan hash value: 1879320302
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 275 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 1 | 78 | 275 (1)| 00:00:01 | 1 | 6 |
|* 2 | TABLE ACCESS FULL | RANGE_PART1 | 1 | 78 | 275 (1)| 00:00:01 | 1 | 6 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART" LIKE '%0')
同样的list partition也可以用like后通配实现pruning:
CREATE TABLE "LIST_PART1"
( "ID" NUMBER,
"EXT" NUMBER,
"PART" VARCHAR2(100)
)
PARTITION BY LIST ("PART")
(PARTITION "P1" VALUES ('00'),
PARTITION "P2" VALUES ('01'),
PARTITION "P3" VALUES ('10'),
PARTITION "P4" VALUES ('11'),
PARTITION "P5" VALUES ('20'),
PARTITION "P6" VALUES ('21')
);
select * from list_part where part like '0%';
Execution Plan
----------------------------------------------------------
Plan hash value: 877256947
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1099K| 11M| 1119 (3)| 00:00:01 | | |
| 1 | PARTITION LIST ALL| | 1099K| 11M| 1119 (3)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL| LIST_PART | 1099K| 11M| 1119 (3)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART" LIKE '0%')
list分区可以使用范围pruning:
select * from list_part where part >'00' and part <='01';
Execution Plan
----------------------------------------------------------
Plan hash value: 903898130
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 10M| 843 (3)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 999K| 10M| 843 (3)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS FULL | LIST_PART | 999K| 10M| 843 (3)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------
###对于hash分区的，只能等值或in list才能pruning,范围查询不行（包括like)
CREATE TABLE "HASH_PART1"
( "ID" NUMBER,
"EXT" NUMBER,
"PART" VARCHAR2(100)
)
PARTITION BY HASH ("PART") partitions 8;
select * from hash_part1 where part='0';
等值的可以分区pruning:
Execution Plan
----------------------------------------------------------
Plan hash value: 3614193290
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION HASH SINGLE| | 1 | 78 | 2 (0)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | HASH_PART1 | 1 | 78 | 2 (0)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART"='0')
in list的使用动态pruning:
select * from hash_part1 where part in ('0','1');
Execution Plan
----------------------------------------------------------
Plan hash value: 2512032245
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION HASH INLIST| | 1 | 78 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | HASH_PART1 | 1 | 78 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART"='0' OR "PART"='1')
范围查询不能pruning:
select * from hash_part1 where part between '0' and '1';
Execution Plan
----------------------------------------------------------
Plan hash value: 904018284
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION HASH ALL| | 1 | 78 | 2 (0)| 00:00:01 | 1 | 8 |
|* 2 | TABLE ACCESS FULL| HASH_PART1 | 1 | 78 | 2 (0)| 00:00:01 | 1 | 8 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART">='0' AND "PART"<='1')
