-- 同志们：尽量让你的查询走索引吧! --

luoyoumou 2011-03-23 01:37:01 


-- 看到很多人，写SQL代码非常随意，想法固然没错：每种方法都可以，只是结果一样就行!

-- 但是，我想：养成良好的SQL风格，会使你终生受益!

-- 因为：有时不同的查询，得到同样的结果，其效率却相差甚远......



-- 请看：

-- 查询一表中昨天生成的数据

-- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mobilefrends_cdate_idx on mobilefrends(cdate);



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

-- 方法一：用to_char()函数



hll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd');



  COUNT(*)

----------

     82119



已用时间:  00: 00: 17.18



执行计划

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

Plan hash value: 3731074549



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

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

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

|   0 | SELECT STATEMENT      |                        |     1 |     8 | 11773  (16)| 00:02:22 |

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

|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 11773  (16)| 00:02:22 |

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



Predicate Information (identified by operation id):

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



   2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy

              -mm-dd'))





统计信息

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

          1  recursive calls

          0  db block gets

      36896  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



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

-- 方法二：用trunc()函数



hll@SZTYORA> select count(*) from mobilefrends where trunc(cdate)=trunc(sysdate-1);



  COUNT(*)

----------

     82119



已用时间:  00: 00: 16.32



执行计划

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

Plan hash value: 3731074549



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

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

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

|   0 | SELECT STATEMENT      |                        |     1 |     8 | 12580  (22)| 00:02:31 |

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

|*  2 |   INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX |   129K|  1012K| 12580  (22)| 00:02:31 |

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



Predicate Information (identified by operation id):

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



   2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))





统计信息

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

          0  recursive calls

          0  db block gets

      36896  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



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

-- 方法三：不用函数

hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate-1) and cdate<trunc(sysdate);



  COUNT(*)

----------

     82119



已用时间:  00: 00: 00.43



执行计划

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

Plan hash value: 2668176725



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

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

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

|   0 | SELECT STATEMENT   |                        |     1 |     8 |     3   (0)| 00:00:01 |

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

|*  2 |   FILTER           |                        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX |    48 |   384 |     3   (0)| 00:00:01 |

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



Predicate Information (identified by operation id):

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



   2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))

   3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))





统计信息

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

          0  recursive calls

          0  db block gets

        221  consistent gets

          0  physical reads

          0  redo size

        345  bytes sent via SQL*Net to client

        338  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



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





-- 由上三种方法可见：

-- 方法一：用to_char()函数，耗时17.18秒，逻辑读为36896次，执行的是全索引扫描；

-- 方法二：用trunc()函数，耗时16.32秒，逻辑读也是36896次，执行的也是全索引扫描；

-- 方法三：不用函数，耗时0.43秒，逻辑读为221次，执行的是索引范围扫描；





-- 请问：哪种方法好呢？







-- 在我的SQL语句中，我的原则是：

-- *(01) 尽量避免隐式类型转换

--      (例如：如果字段是日期类型的，我会在where语句等式左边用字段原型，而尽量在等式右边用函数，

               绝不会在左边用函数转换成字符再去与右边比较)；

-- *(02) 尽量少用函数，同样一件事情，能够用两个函数就能解决的，我绝不会嵌套三个函数；

--      (例如：求上个月的最后一天，我会用trunc(sysdate,'mm')-1，而不用last_day(add_months(sysdate,-1)) )

-- *(03) 在所有的存储过程中，我会尽量用绑定变量，以避免硬解析带来的资源消耗!

-- *(04) 在所有的存储过程中，能够用SQL语句的，我绝不会用循环去实现!

--      (例如：列出上个月的每一天，我会用connect by去递归查询一下，绝不会去用循环从上个月第一天到最后一天)

-- *(05) 当有一批处理的插入或更新时，我会用批量插入或批量更新，绝不会一条条记录的去更新!
...全文
848 38 打赏 收藏 转发到动态 举报
写回复
用AI写文章
38 条回复
切换为时间正序
请发表友善的回复…
发表回复
Dekey_1314 2013-08-26
  • 打赏
  • 举报
 回复
当要查询的数据与表中所有的数据相比，所占比例达到一定程度时，Oracle就会采取全表扫描而不走索引!

在Oracle Exadata平台下不提倡使用索引（因为增强了全盘扫描能力，我个人也不喜欢用索引），因为所查数据占总数据5%以上时，效率反而更低（5%是我在上个项目测试得出的数据，不一定准确）
szyanbin 2013-07-27
  • 打赏
  • 举报
 回复
楼主好人一生平安
mingchaoyan 2011-06-11
  • 打赏
  • 举报
 回复
楼主是好人，接分
deadendflcl 2011-06-08
  • 打赏
  • 举报
 回复
研究一下，索引一致弄不清楚~
sad_hopedream 2011-06-08
  • 打赏
  • 举报
 回复
仰慕大侠来了！
aierda 2011-06-08
  • 打赏
  • 举报
 回复
索引范围扫描是什么意思呢?
aierda 2011-06-08
  • 打赏
  • 举报
 回复
方法三也用到了trunc函数啊,怎么效率会高那么多呢?不明白,请大家指点
tony2009 2011-03-30
  • 打赏
  • 举报
 回复
哎，我就是楼主说的那种，看来以后要注意了。。。
SF_7_Hellen 2011-03-30
  • 打赏
  • 举报
 回复
谢谢 太棒了
kingtiy 2011-03-30
  • 打赏
  • 举报
 回复
索引是个好东西,用的好可以提高N倍性能.用的不好,有时还不如不用.
hanzs 2011-03-30
  • 打赏
  • 举报
 回复
索引这个东西太大了，有好有坏吧，具体看实际业务要求了，不能以一概全
但是SQL书写规范还是很重要的……
至于索引列要不要函数运算，我觉得业务需求确实要，那就直接建函数索引，并不是一定不能进行函数运算
getmydream 2011-03-29
  • 打赏
  • 举报
 回复
赞，确实如此，尽量少在查询条件中对主键或者INDEX列进行函数运算！
王向飞 2011-03-29
  • 打赏
  • 举报
 回复
以后注意。。
这次为接分而来。。。。
物润声无 2011-03-27
  • 打赏
  • 举报
 回复
感谢楼主！
kyle.tian 2011-03-26
  • 打赏
  • 举报
 回复
一直提倡用index
palm_civet 2011-03-26
  • 打赏
  • 举报
 回复
这个看情况了，批量有时候可以不用索引
oracle_dba_11 2011-03-24
  • 打赏
  • 举报
 回复
呵呵，一直都是这样用的
Dave 2011-03-24
  • 打赏
  • 举报
 回复


走索引是一方面，还有一方面要保证表的统计信息是准确的，如果表的统计信息不准确，那SQL的性能也就不能保证了。

zty598416146 2011-03-24
  • 打赏
  • 举报
 回复
来膜拜来的呵呵 不错的测试！
304的的哥 2011-03-23
  • 打赏
  • 举报
 回复
[Quote=引用楼主 luoyoumou 的回复:]
SQL code

-- 看到很多人，写SQL代码非常随意，想法固然没错：每种方法都可以，只是结果一样就行!
-- 但是，我想：养成良好的SQL风格，会使你终生受益!
-- 因为：有时不同的查询，得到同样的结果，其效率却相差甚远......

-- 请看：
-- 查询一表中昨天生成的数据
-- 原表mobilefrends中的cdate字段上有索引，创建索引语句是：create index mo……
[/Quote]
当然是执行时间最少的好啦！
接分来的......嘿嘿
加载更多回复（11）
oracle bin开头的索引,- 同志们：尽可能让你的查询走索引吧! -
-- 同志们：尽量让你的查询走索引吧! ----看到很多人，写SQL代码非常随意，想法固然没错：每种方法都可以，只是结果一样就行!--但是，我想：养成良好的SQL风格，会使你终生受益!--因为：有时不同的查询，得到同样的结果，其效率却相差甚远......--请看：--查询一表中昨天生成的数据--原表mobilefrends中的cdate字段上有索引，创建索引语句是：createind...
MySQL大数据量分页查询方法及其优化 ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N ---适
MySQL大数据量分页查询方法及其优化 ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N ---适应场景: 适用于数据量较少的情况(元组百/千级) ---原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)
快速定位mysql是否走索引
快速定位mysql是否走索引 文章目录快速定位mysql是否走索引问题背景：当我们查询sql的时候很慢，不知道什么原因，是否在走索引解决办法步骤1. 依赖表结构及测试数据：2. 基本关联查询sql3. 查看执行计划4. 增加索引 问题背景：当我们查询sql的时候很慢，不知道什么原因，是否在走索引 解决办法步骤 1. 依赖表结构及测试数据： CREATE TABLE `student` ( `student_id` int(11) DEFAULT NULL, `student_name` varcha
oracle date 走索引,Oracle根据to_date(literal)格式使用或忽略索引列
好的 – 我会试一试,这主要是从可用信息中扣除的：为什么Oracle选择不同的执行计划？在您的第二个查询中,似乎有不寻常的日期格式,优化器不知道结果日期的值是什么.你看到过滤器谓词：1 – 过滤器(TO_DATE(‘20140610′,’yyyymmdd’)< = TO_DATE('2014-06-10 23:59:59','syyyy-mm-dd hh24：mi：ss')) 这意味着优化器...
oracle如何让like走索引,让完全like也走索引
今天开发的传一条sql语句，查询很慢，半天都不返回数据，其中一张表的where 条件只有一个完全(CUSTOMER_TRUE_NAME llike %xxxxxxx%) ，导致不走此列上的索引，但又不得不使用完全like,此表又很大，还好查询只需要三个列，所以使用查询需要的三个列创建索引后，再查询就走索引了SQL> select c.CUSTOMER_xxxx_NAME,c.MOBILE...
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章