升星，散分，来者有分！写好SQL（1）

[hebo2005]

写好SQL
一、 基本SQL优化
本节谈的主要是基于RBO（RULE BASED OPTIMIZER）的SQL 优化
Select * 和select 字段1，字段2。这两种写法，前者对写的人来说比较简单，后者则要多点点东西，而对于性能来，一般都是建议写具体字段的，ORACLE在解析的过程中， 会将‘*’ 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。
From 表1 别名，表2 别名。一般来说基于rbo的方式，ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表 driving table）将被最先处理。 在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行派序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。另外给表名加上简洁的别名，也是不错的习惯，一是在多表关联时写起来方便，减少写错的机会，另一方面解析执行的时候，也相对快一点。
Where 条件1 and 条件2。ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
GROUP BY　提高GROUP BY 语句的效率， 可以通过将不需要的记录在GROUP BY 之前过滤掉。
order by 字段名或者order 1（列），2(列)。Order by是比较费时间的操作，所以尽可能在语句减少各子句内的使用，也尽可能在索引列上进行排序。
在写完SQL语句，对于那些数据量比较大的语句，应该多用下执行计划来分析下语句，看看语句是否可以有优化的地方，一般来说，最常见的SQL语句慢，是因为进行了全表扫描，在进行WHERE过滤数据时，有索引列的就尽可能在索引列上过滤，这样会快很多，也尽可能避免写使索引列过滤失效的语句。在数据比较的语句中，如果类似于>10之类的，尽可能用>=11来代替，两者的区别在于，>10的操作，需要先找到＝10的，然后再开始找>10的第一条纪录，而后者是直接跳到11这条纪录开始。

二、 Rownum和row_number() over()的使用
ROWNUM是oracle从8开始提供的一个伪列，是把SQL出来的结果进行编号，始终从1开始，常见的用途就是用来分页输出.
比如

SELECT *
  		FROM torderdetail a
 	WHERE ROWNUM <= 10 

这条语句就是输出前10条纪录，在这里用途上类似于sql sever的top，不过rownum对于指定编号区间的输出应该说更强大

SELECT *
  		FROM (SELECT a.*, ROWNUM rn
          	FROM torderdetail a)
 	WHERE rn >= 10 AND rn <= 20

这条语句即是输出第10到第20条纪录，这里之所以用rownum rn，是把rownum转成实例，因为rownum本身只能用<=的比较方式，只有转成实列，这样就可做 >=的比较了。
在实际用途中，常常会要求取最近的几条纪录，这就需要先对纪录进行排序后再取rownum<=
一般常见的

SELECT *
 	 FROM (SELECT   a.*
            FROM torderdetail a
        ORDER BY order_date DESC)
 WHERE ROWNUM <= 10 

而在CSDN曾经发生过讨论，关于取近的10条纪录，有人给出这样的语句

SELECT   a.*
    		FROM torderdetail a
   		WHERE ROWNUM <= 10
ORDER BY order_date DESC 

之所以会出现这样的语句，主要是从效率上的考虑，前面条语句，是要进行全表扫描后再排序，然后再取10条纪录，后一条语句则不会全表扫描，只会取出10条纪录，很明显后条语句的效率会高许多。
那为什么会有争议呢，那就在于在执行顺序上争议，是先执行排序取10条纪录，还是取10条纪录，再排序呢？两种顺序取出来的结果是截然相反的，先排序再取10条，就是取最近的10条，而先取10条，再排序，则取出的最早的10条纪录。对于此语句，普遍的认为执行顺序是先取10条纪录再排序的。所以此语句应该是错误。但实际上并非如此，此语句的执行顺序和order by的字段有关系，如果你order by 的字段是pk，则是先排序，再取10条（速度比第一种语句快），而排序字段不是PK 时，是先取10条再排序，此时结果就与要求不一样了，所以第二种写法一定要在排序字段是主键的情况下才能保证结果正确。
Row_number() over()这个分析函数是从9I开始提供的，一般的用途和rownum差不多。
一般写法row_number() over( order by order_date desc) 生成的顺序和rownum的语句一样，效率也一样（对于同样有order by 的rownum语句来说），所以在这种情况下两种用法是一样的。
而对于分组后取最近的10条纪录，则是rownum无法实现的，这时只有row_number可以实现，row_number() over(partition by 分组字段 order by 排序字段)就能实现分组后编号，比如说要取近一个月的每天最后10个订单纪录

SELECT *
  		FROM (SELECT a.*,
ROW_NUMBER () OVER (PARTITION BY TRUNC (order_date) ORDER BY order_date DESC)
                                                                           rn
          FROM torderdetail a)
 WHERE rn <= 10 

Rownum的另类用法，有时候我们会遇到这种需求，要求输出当月的所有天数，许多人会烦恼，数据库里又没有这样的表，怎么输出一个月的所有天数呢？用rownum就能解决：

SELECT     TRUNC (SYSDATE, 'MM') + ROWNUM - 1
      FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), 'dd')) 

[gxhsnowflower]

写的好，收藏了

[wj6625]

学习到了！

[ilovemk]

支持hebo，支持散分，散分万岁，不散分可耻。。。

BTW:CSDN是不是不能发表情的哦，刚刚来到。。。

[阿三]

接分，我也想要颗星

[JavaCoolMan]

我接不住了 忽忽~~

[yun222222]

接分~~~~~~~~~~~~~

[酒肉伺候]

好东西，收藏了！

[xm_bluesky]

真的很好哦

[yuxingye]

接分

[hebo2005]

刚发现，技术区居然不能平均给分，算了下，现在是85人回复，自己回复+本条为5条，凑足100个有效回复（去掉我自己的）结贴

[priwilliam]

顶一下 走你~

[呵-春泥]

什么都不说，只接分……

[lyyshui]

up！

[lvjx125]

JF

[lifanfly]

学习中

[hxmsammi]

[Quote=引用 65 楼 hebo2005 的回复:]
引用 59 楼 hxmsammi 的回复:
Rownum的另类用法，有时候我们会遇到这种需求，要求输出当月的所有天数，许多人会烦恼，数据库里又没有这样的表，怎么输出一个月的所有天数呢？用rownum就能解决：

SQL code
SELECT TRUNC (SYSDATE, 'MM') + ROWNUM - 1
FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), 'dd'))


CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (LAST_DAY …
[/Quote]

恩试过了，在本地搭建的数据库里加了＝就会将6月1号的取进来，但是连接服务器端的数据库加了＝不会将6月1号的数据取进来，我自己也觉得很怪啊

[arsenal1999]

楼主好强大

[kidim]

我也来接分
发现都看不懂了
得回去再看看基础知识再来学习了

[老彭学java]

即学知识，又得分，感觉比楼主还高兴啊．

[貓哥是個傳說]

100~

來晚了，占個位