分区裁剪 oracle,Oracle 分区裁剪

explain plan for select count(*) from esb2_trans_log t

where t.trans_date >= sysdate - 3 / 1440;

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

1 Plan hash value: 287218081

2

3 ------------------------------------------------------------------------------------------------------------

4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

5 ------------------------------------------------------------------------------------------------------------

6 | 0 | SELECT STATEMENT | | 1 | 8 | 878K (1)| 02:55:42 | | |

7 | 1 | SORT AGGREGATE | | 1 | 8 | | | | |

8 | 2 | PARTITION RANGE ITERATOR| | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |

9 |* 3 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |

10 ------------------------------------------------------------------------------------------------------------

11

12 Predicate Information (identified by operation id):

13 ---------------------------------------------------

14

15 3 - filter("T"."TRANS_DATE">=SYSDATE@!-.002083333333333333333333333333333333333333)

select * from table(dbms_xplan.display_cursor('btfkf4uww4zv4'));

PLAN_TABLE_OUTPUT

SQL_ID btfkf4uww4zv4, child number 0

-------------------------------------

select count(*) from esb2_trans_log t where t.trans_date >= sysdate

- 3 / 1440

Plan hash value: 287218081

------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 878K(100)| | | |

| 1 | SORT AGGREGATE | | 1 | 8 | | | | |

| 2 | PARTITION RANGE ITERATOR| | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |

|* 3 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 177 | 1416 | 878K (1)| 02:55:42 | KEY | 9 |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("T"."TRANS_DATE">=SYSDATE@!-.002083333333333333333333333333333333333333)

explain plan for select *

from esb2_trans_log t

where t.trans_date >=

to_date('2018-05-11 08:00:00', 'yyyy-mm-dd hh24:mi:ss');

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

1 Plan hash value: 2926439834

2

3 -----------------------------------------------------------------------------------------------------------

4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

5 -----------------------------------------------------------------------------------------------------------

6 | 0 | SELECT STATEMENT | | 155 | 32395 | 2 (0)| 00:00:01 | | |

7 | 1 | PARTITION RANGE ITERATOR| | 155 | 32395 | 2 (0)| 00:00:01 | 8 | 9 |

8 |* 2 | TABLE ACCESS FULL | ESB2_TRANS_LOG | 155 | 32395 | 2 (0)| 00:00:01 | 8 | 9 |

9 -----------------------------------------------------------------------------------------------------------

10

11 Predicate Information (identified by operation id):

12 ---------------------------------------------------

13

14 2 - filter("T"."TRANS_DATE">=TO_DATE(' 2018-05-11 08:00:00', 'syyyy-mm-dd hh24:mi:ss'))

分区表有很多好处，以大化小，一小化了，加上并行的使用，在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反，效果比普通表跟糟糕。

为了更好的使用分区表，这里看一下分区表的执行计划。

PARTITION RANGE ALL：扫描所有分区

PARTITION RANGE ITERATOR：扫描多个分区，小于所有个分区数量

PARTITION RANGE SINGLE：扫描单一的分区

KEY，表示执行时才知道哪个分区