今天在测试开发代码的时候,发现在分区健上做了如下操作:
select count(0) from endss.en_wto_cookie_type_fatdt0 where trunc(yyyymmdd) = trunc(sysdate);
其中yyyymmdd是表cookie的分区健,且格式是yyyymmdd
这句代码有什么问题了?通过查询执行计划,可以看出,这样写sql,就不会用到分区健了,会进行全分区扫描,影响性能。
自己试验了一次,来证明一下这个问题
DROP TABLE order_act;
CREATE TABLE ORDER_ACT
(
ORDER_ID NUMBER(7) NOT NULL,
yyyymmdd DATE
)
PARTITION BY RANGE (yyyymmdd)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('20100502','yyyymmdd')) ,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('20100503','yyyymmdd')) ,
PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('20100504','yyyymmdd'))
)
INSERT INTO order_act VALUES(1,to_date('20100501','yyyymmdd'));
INSERT INTO order_act VALUES(2,to_date('20100501','yyyymmdd'));
INSERT INTO order_act VALUES(3,to_date('20100502','yyyymmdd'));
INSERT INTO order_act VALUES(4,to_date('20100502','yyyymmdd'));
INSERT INTO order_act VALUES(5,to_date('20100503','yyyymmdd'));
INSERT INTO order_act VALUES(6,to_date('20100503','yyyymmdd'));
EXPLAIN PLAN FOR
SELECT COUNT(0) FROM order_act WHERE yyyymmdd = to_date('20100503','yyyymmdd');
COMMIT;
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
SELECT COUNT(0) FROM order_act WHERE trunc(yyyymmdd) = to_date('20100503','yyyymmdd');
COMMIT;
SELECT * FROM TABLE(dbms_xplan.display);
第一个执行计划:
第二个执行计划:
pstart,pstop:开始,结束的分区号