笔记_查询优化，及一些细节

有道云笔记 连接：

http://note.youdao.com/noteshare?id=c39dac3e05f974c819a180f14e92f114

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

以下可忽略：

优化，一些常用技巧：

1.开发中SELECT * 不要用

        应指明具体字段，效率差别比较大（数据量大后）。代码中不应该出现这样的查询（SELECT *）。

2.查询多个条件时建议用 in( ，， )

        eg:查询员工号为123、324或550的员工信息：SELECT * FROM emp WHERE empno IN(123,324,550);

        oracle对in(，，)这种方式的查询有优化，效率特别高。

3.一次 insert多条数据

        INSERT INTO dept 

                    SELECT '10','accounting','newyork' FROM dual

                                UNION SELECT '20','sale','boston' FROM dual;

        跟mysql不同点是oracle中select后必须要有from。但是下面的方式不行：

        INSERT INTO dept VALUES(,,),(,,);

4.复制粘贴列

        a.从另一个表，条件很苛刻：两个字段类型相同，并且粘贴需要依据（可以是主外键，也可以只有几个满足条件）：

UPDATE 表1 SET 表1.a=表2.b WHERE 表1.id=表2.id；

        b.从本表，比较简单：

                UPDATE emp SET siredate=to_char(airedate,'yyyy-mm-dd');

        “复制粘贴列”是批量update的一个应用场景。

5.改变列顺序

        a.通用的方法：新建表（按新的列顺序），然后用INSERT INTO ... SELECT ..,..,..FROM...从原表批量复制（按新的列顺序）insert到新表中；最后，删除酒标，将新表改名回去。

        b.操作Oracle数据字典。

6.查找第x行数据（ROWNUM和ROWID）    

        ROWNUM和ROWID是Oracle每个表都有两个字段，其中 ROWNUM是隐藏的，ROWID是可以查询到的。

        

        ROWNUM：隐藏字段， 无法直接作为where条件（这时只能下述方式嵌套查询）。

        ROWID：唯一标识记录物理位置（表空间中）的一个id，由data_object_id# rfile# block# row#组成。

        

        Oracle的行号相关查询，是个蛋疼无比的地方，因为语法上你根本不懂；你仅能找出一些规律，不至于在项目中被弄崩溃。 总结：

        a. 非子查询适用的 唯一现实需求：显示所有记录的行号。（ 不要带任何WHERE条件）

        

         实验：非子查询带WHERE的情况：

        

         结论：以上混乱程度可见，WHERE中带ROWNUM是很不靠谱的，所以还是子查询吧。

        b.子查询方式。（ 完美解决分页、查行号，搜第几条记录）

        

7.少使用DISTINCT去重复。

        a.使用DISTINCT会底层使用 排序，大数据量时降低效率。

        b.DISTINCT作用有限，只能保证结果集（1或多个字段）的不重复，却无法进行诸如“查询emp表中职位（job）的种数”这样的功能。        

        select distinct name from t1 能消除重复记录，但只能取一个字段，现在要同时取id,name这2个字段的值。
        select distinct id,name from t1 可以取多个字段，但只能消除这2个字段值全部相同的记录。

        替代DISTINCT方法：本笔记-->练习-->复杂查询_统计-->聚合函数-->4。

8.WHERE子句多个条件时，Oracle是从右往左扫描。

        

        这是很容易犯错的地方，也算Oracle的一个常识了吧。

        比如，查询条件有工作地址为“北京”、性别为“男”、电话号为“137”开头，那么， 性别一定要写在最后。因为这样Oracle先根据性别筛选一次，之后再依次根据工作地址、电话号筛选时（ 记录量已经少了一半），效率就会高很多。

        Oracle从右往左扫描，正好跟人从左往右的书写顺序相反，某种程度上正是为了 契合人的书写顺序。因为人在从左往右写SQL语句时，大脑的一般思路都是先想到最具体的条件，然后是更宽泛的条件。比如，英文的地址：Chang An Avenue, BeiJing, China. 

        补充：貌似错了，左右对效率没有影响！

        网上有人的实验： http://bbs.csdn.net/topics/380239959，结论是：

        “实际上，从这条SQL语句的执行计划来分析， Oracle首先会找出条件中使用索引或表间连接的条件，以此来过滤数据集，然后对这些结果数据块所涉及的记录逐一检查是否符合所有条件，所以条件顺序对性能几乎没有影响。”“实验一：证明了SQL的语法分析是 从右到左的。“