Oracle 分区裁剪
发布时间:2020-12-12 13:57:02 所属栏目:百科 来源:网络整理
导读: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_OUTPUT1 Plan hash value: 2872180812 3 ---------------------------------------------------
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,表示执行时才知道哪个分区 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |