1.避免对列进行运算
在编写SQL时,要尽量避免对列进行运算,要善于通过等价改写消除对列运算的SQL,
这样可以避免索引无法使用,从而提高语句效率。点击(此处)折叠或打开
- DROP TABLE ljb_test;
- CREATE TABLE ljb_test(ID NUMBER, log_time DATE);
- INSERT INTO ljb_test SELECT ROWNUM, SYSDATE - ROWNUM FROM dual CONNECT BY ROWNUM <= 1000000;
- CREATE INDEX idx_ljbtest_logtime ON ljb_test(log_time);
-
- --下面的查询无法使用索引
- SELECT * FROM ljb_test WHERE TRUNC(log_time) = to_date(\'2013-12-25\', \'YYYY-MM-DD\');
-
- ID LOG_TIME
- ---------- ---------
- 6 25-DEC-13
![](http://img.blog.itpub.net/blog/attachment/201401/15/17013648_1389763372AR3u.png?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201401/15/17013648_1389763404wCkK.png?x-oss-process=style/bb)
点击(此处)折叠或打开
- --使用索引, 逻辑读显著下降
- SELECT * FROM ljb_test
- WHERE log_time >= to_date(\'2013-12-25\', \'YYYY-MM-DD\')
- AND log_time < to_date(\'2013-12-25\', \'YYYY-MM-DD\') + 1;
-
- ID LOG_TIME
- ---------- ---------
- 6 25-DEC-13
![](http://img.blog.itpub.net/blog/attachment/201401/15/17013648_1389763486CJ41.png?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201401/15/17013648_1389763499BZ5y.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1070877/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1070877/