======================================================================================
SQL优化的一般原则
=================================
1.在SQL查询语句中，查询所有的列时尽量不要使用"*"号。
-------------------------------------------------------------------------------------
因为如查使用了*号查询所的列，那么数据库须要额外的去查询数据字典，把*号转换为表的所有列,再将查询结果返回给用户。
------------------------------------------------------------------------------------------------------------------------------
2.编写SQL时使用相同的编码风格，尽可能的减少解析的次数。
-------------------------------------------------------------------------------------
3.如果是删除表中所的记录，可以使用TRUNCATE语句代替DELETE语句。提高执行的效率。
-----------------------------------------------------------------------------------------------------------------------------
4.在确保业务逻辑的前提下及时COMMIT提交事务，释放事务占用的资源。
-----------------------------------------------------------------------------------------------------------------------------
5.EXISTS/IN
------------------
1.EXISTS:
先执行主查询，再运行子查询直到找到第一个匹配项。
---------------------------------------------------------------------------------------------------------
2.IN:
先对子查询中的表做全表扫描，获得查询结果，然后再执行主查询。
---------------------------------------------------------------------------------------------------------
总结:EXISTS适合外表小而内表大的情况；IN适合外表大而内表小的情况。
另外，NOT IN操作符也是不建议使用的，因为它不能使用索引，建议使用
NOT EXISTS或外连接替代NOT IN。
-------------------------------------------------------------------------------------------------------
6.尽量用EXISTS代替DISTINCT
--------------------------------------------
例:
低效的SQL:
-----------------
select distinct e.deptno,d.dname from emp e,dept d
where e.deptno=d.deptno;
-------------------------------------------------------------
高效的SQL:
----------------
select deptno,dname from dept d
where exists(
select 1 from emp e where e.deptno=d.deptno);
---------------------------------------------------------------------
7.在非必须时，使用UNION ALL代替UNION
------------------------------------------------------------
UNION操作符在运行时先取出两条SQL结果集，并对结果集进行排序运算，去掉重复记录，
再返回最终结果。而UNIOL ALL操作只是简单的将两个结果集合并后直接返回，没有排序
操作。所以，在非必须的情况下，可以使用UNION ALL省去这一部分资源消耗。
--------------------------------------------------------------------------------------------------------------------------
8.在保证功能的前提下，减少对表的查询次数
-----------------------------------------------------------
例:
低效的SQL:
----------------
select ename,job,sal,deptno from emp
where
job=(select job from emp where ename='SCOTT')
and
deptno=(select deptno from emp where ename='SCOTT');
-----------------------------------------------------------------------------
高效的SQL:
--------------
select ename,job,sal,deptno from emp
where (job,deptno)=
(select job,deptno from emp where ename='SCOTT');
-----------------------------------------------------------------------
9.尽量使用表的别名，并在列前标注来自哪个表
------------------------------------------------------------------------------
10.不要使用HAVING子句去实现WHERE子句的功能
------------------------------------------------------------------------------
在使用分组函数时，最好在分组前用WHERE子句过滤掉尽可能多的记录。
------------------------------------------------------------------------------------------------------
例:
低效的SQL:
----------------
select job,avg(sal) from emp
group by job
having job <> 'PERSIDENT';
------------------------------------------
高效的SQL:
-----------------
select job,avg(sal) from emp
where job <> 'PERSIDENT';
group by job;
------------------------------------------
在表连接中的优化原则
============================
1.识别驱动表，并将数据量最小的表作为驱动表
-------------------------------------------------------------
驱动表是指在SQL语句中最先被访问的表，通常是以全表扫描的方式访问。Oracle会检查语句中
每个连接表的大小和索引状态，选择开销最低的方式去执行。
-------------------------------------------------------------------------------------------------------------------------------
2.如果是多个连接条件的查询，将过滤掉最多记录的条件放在最后面。
-------------------------------------------------------------------------------------------------------------------------------------
合理使用索引
============================
1.全表扫描
-------------------------------
可能引起全表扫描的操作:
----------------------------------
1.所查询的表没有索引
-----------------------------------------------------------------------------
2.没有查询条件(WHERE子句)
-----------------------------------------------------------------------------
3.查询条件中的列没有索引
-----------------------------------------------------------------------------
4.查询条件中，对索引列使用了函数或者算数表达式
如:UPPER,NOT IN,IS NULL,%等。
-----------------------------------------------------------------------------
2.尽量避免使用LIKE操作符
---------------------------------------
引起全表扫描的LIKE查询:LIKE '%SALES%'
-----------------------------------------------------------
引用了范围索引的LIKE查询:LIKE 'SALES%'
-----------------------------------------------------------
3.在WHERE子句中避免使用IS NULL，IS NOT NULL，<>,!=,否则引起全表扫描。
--------------------------------------------------------------------------------------------------------------
4.避免对在表查询中的列使用单行函数或算数表达式
---------------------------------------------------------------------
单行函数对于数据类型转换是常用和实用的功能，但是用在SQL语句中，如果索引不是基于函数的，当在WHERE子句中对索引列使用函数时，索引不再起作用，所以应尽量避免，或者将索引建在函数上。
-------------------------------------------------------------------------------------------------------------------------------
例:
1.birthday列上上索引，则将:
--------------------------------------
where trunc(birthday,'YEAR') = '1988';
------------------------------------------------
优化处理为:
---------------
where birthday >= '1-1 月-1988'
and
birthday <= '31-12月-1988';
-----------------------------------------------
2. 避免在索引列上使用计算，否则也会导致索引失败而进行全表扫描。
------------------------------------------------------------------------------------------------
where birthday + 30 =sysdate;
--------------------------------------
优化处理为:
----------------
where birthday = sysdate - 30;
--------------------------------------
3.对于连接多个列的操作符||，也会导致索引失效。
-------------------------------------------------------------------
where studentID || classID = '123456';
---------------------------------------------
优化处理为:
-----------
where studdentID = '123' and classID = '456';
---------------------------------------------
4.进行隐式数据类型转换时，也会使索引失效
--------------------------------------------------------------
where ProductID = 123456;
--------------------------------------
优化处理为:
-----------
where ProductID = '123456';
--------------------------------------
5.对于ORDER BY语句中的列避免使用函数或算数表达式
------------------------------------------------------------------------------
6.总结:任何对列的操作，如函数，算数表达式等，都会导致全表扫描，所以查询时尽量将操作移动到等号右边。
------------------------------------------------------------------------------------------------------------------------------------
Oracle优化器和执行计划
================================
SQL语句被Oracel Server收到后，从分析到执行的步骤如下:
-------------------------------------------------------------------------------
1.检查SQL语句的语法是否正确
-----------------------------------------------------------------------------
2.核实SQL中的表名和列名是否正确
-----------------------------------------------------------------------------
3.检查概要(Profile)，确定SQL语句是否已经存在执行计划。
-----------------------------------------------------------------------------
4.如果没有，则生成执行计划，使用基于成本的优化规则(CBO,Cost-Based Optimizer),
根据数据字典中的统计数据来决定最终的执行计划。
---------------------------------------------------------------------------------------------------------------
5.基于执行计划，为SQL语句生成二进制执行代码
-----------------------------------------------------------------------------
6.执行二进制代码
-----------------------------------------------------------------------------
AutoTrace之查看执行计划
===============================
1.设置跟踪SQL
-------------------------------
set autotrace on/off/traceonly [explain|statistics]
----------------------------------------------------------
2.创建PLAN_TABLE和角色PLUSTRACE
-------------------------------
1.创建PLAN_TABLE
---------------------------------
SQL>@ ?/rdbms/admin/utlxplan.sql
---------------------------------------------
2.创建PLUSTRACE角色
----------------------------------
SQL>create role plustrace;
---------------------------------------------------------------
SQL>grant select on v_$sesstat to plustrace;
SQL>grant select on v_$statname to plustrace;
SQL>grant select on v_$mystat to plustrace;
--------------------------------------------------------------
SQL>grant plustrace to dba with admin option;
--------------------------------------------------------------
3.将角色PLUSTRACE赋给用户
-------------------------------------------
SQL>grant plustrace to scott;
SQL>alter user scott default role connect,resource,plustrace;
-------------------------------------------------------------------------
3.查看执行计划
----------------------------
SQL>conn scott/lisheng
SQL>set autotrace on;
---------------------------------------------
单表访问路径
================================
1.从物理层面看，一次读取的最小单位是Oracle数据块，一次读取得最大值由操作系统的I/O最大值
和multiblock参数共同决定。
------------------------------------------------------------------------------------------------------------------------------------
2.从逻辑上来看，Oracle通过以下几种方式访问数据表:
------------------------------------------------------------------------
1.Full Table Scan
-----------------------------------------
全表扫描模式下会读取表中的所有数据行，直到表的高水位线。
(High Water Mark,HWM.即表示曾经写入过的最后那个数据块)
如果曾经使用DELETE语句删除表中的全部数据，HWM不会清除，全表扫描仍然会扫描到HWM的位置，如果使用TRUNCATE删除数据，HWM会回退到表的初始位置。
-------------------------------------------------------------------------------------------------------------------------
2.ROWID Lookup
-----------------------------------------
3.Index Lookup
-----------------------------------------
1.索引扫描由两步完成，分别为:
---------------------------------------------------
1.扫描索引得到对应的ROWID值
---------------------------------------------------------------------------
2.通过找到的ROWID从表中读出具体的数据
---------------------------------------------------------------------------
注意:这两个步骤中的每一个都是单独的一次I/O
---------------------------------------------------------------------
2.索引扫描的五种方式:
---------------------------------
1.INDEX UNIQUE SCAN(索引唯一扫描)
-----------------------------------------------------
唯一索引意味着返回单个ROWID。如果唯一索引是组合索引，则至少要有该组合
索引的引导列参与到查询中。但是否是索引唯一扫描，取决于是否返回单条记录
-------------------------------------------------------------------------------------------------------------
2.INDEX RANGE SCAN(索引范围扫描)
------------------------------------------------------
当使用索引存取多行数据时
例:
SQL>select * from dept where deptno>10;
----------------------------------------------------------------------
3.INDEX FULL SCAN(索引全局扫描)
--------------------------------------------------------
与FTS类似，当获取数据量占整个表的数据量比较大时，而且在WHERE条件中使用索引查询，CBO判断全索引扫描比全表扫描获得数据更有效，将进行全索引扫描，并且要保证此时查询出的数据必须从索引中可以获取到。因为索引是排序的，所
以查询的结果集也是排序的。
-------------------------------------------------------------------------------------------------------------
例:
SQL>select empno from emp;
----------------------------------------------------------------------
4.INDEX FAST FULL SCAN(索引快速全局扫描)
--------------------------------------------------------------
扫描索引中的所有数据块，与INDEX FULL SCAN类似，但不同点是它对查询结果
不排序，目的是利用多块或者并行读入，获得最大吞吐量，从而缩短执行时间。
当对有索引的大表作COUNT(*)操作时，经常发生。
-------------------------------------------------------------------------------------------------------------
5.INDEX SKIP SCAN(索引跳跃扫描)
-------------------------------------------------------------
JOIN AND ROW SOURCE
==============================
在Oracle的官方文档中，用来JOIN的表被称作ROW SOURCE。
--------------------------------------------------------------------------------------
以下讨论以等值连接为主展开:
------------------------------------------
两个ROW SOURCE之间有三种典型的JOIN方式:
--------------------------------------------------------------------
1.Nested loop Joins(嵌套循环连接,NL)
--------------------------------------------------------------
嵌套循环连接的方式是确定一个驱动表为Outer Table，另一个表作为Inner Table,
驱动表中的每一行与Inner表中的相应记录做连接。
-------------------------------------------------------------------------------------------------------------
从嵌套循环的内部连接过程来看，需要用ROW SOURCE1中的每一行，去探查(匹配)
ROW SOURCE2中的所有行，所以要求ROW SOURCE1尽可能的小与高效访问ROW SOURCE2，它们是影响连接效率的关键，这样做的目的是使连接操作产生最少的物理I/O次数。
------------------------------------------------------------------------------------------------------------------
2.Hash Joins(哈希连接,HJ)
---------------------------------
哈希连接一般用于连接大数据表。
----------------------------------------------------------------------------
这种连接是在oracle 7.3以后引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO
优化器中。较小的row source被用来构建hash table与bitmap，第2个row source被用
来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种
连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动
表，当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。
-------------------------------------------------------------------------------------------------------------------
3.Sort Merge Joins(排序合并连接,SMJ)
------------------------------------------------------
SORT MERGE JOIN用来连接两个ROW SOURCE，大多数情况下HASH JOIN的性能要优于SORTMERGE JOIN，但如果是两个ROW SOURCE已排好序，或者不需要排序的情况下，SORT MERGE JOIN的性能要好于HASH JOIN。一旦SORT MERGE JOIN中包含了比较慢的访问方式，如全表扫描，这种连接方式的效率会非常低。
------------------------------------------------------------------------------------------------------------------
其它连接方式的原理类似于等值连接。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25963200/viewspace-701744/，如需转载，请注明出处，否则将追究法律责任。
