嵌套SQL的查询速度比较分析

嵌套SQL的查询速度比较分析

文章中使用Oracle自带的HR数据库，故代码可以直接进行测试。
代码一：
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'A%'
or t.first_name like 'B%'
or  t.first_name like 'H%'
or  t.first_name like 'K%'
or  t.first_name like 'M%'
or  t.first_name like 'J%'
or  t.first_name like 'N%'
执行计划：


代码二：
select *
from (
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'A%'
or t.first_name like 'B%'
or  t.first_name like 'H%'
or  t.first_name like 'K%'
or  t.first_name like 'M%'
or  t.first_name like 'J%'
or  t.first_name like 'N%'
)
执行计划：


对比：代码1与代码2的执行计划相同

代码三：
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'A%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'B%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'H%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'K%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'M%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'J%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'N%'

执行计划：


代码四：
select * from
(
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'A%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'B%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'H%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'K%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'M%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'J%'
union
select t.employee_id, t.first_name, t.phone_number  from HR.Employees t where t.first_name like 'N%'
)
执行计划：

对比：代码4中Sort Unique与代码3的执行计划相同。但Oracle在处理代码4的查询语句时，构建了一个内部视图来整理查询结果，其中需要21次IO操作，故需要更长的时间。

其他：在一个SQL中，使用“OR”语句比使用多个Union会花费更短的时间。

代码五：
代码5-1：
select *
from
(select *  from HR.Employees tx where tx.department_id = 50) T1,
(select * from HR.Departments ty where ty.department_id < 150) T2
where t1.department_id = t2.department_id
代码5-2：
select *
from
HR.Employees t1,
HR.Departments T2
where t1.department_id = t2.department_id and t1.department_id = 50 and t2.department_id < 150
代码5-3：
select *
from
HR.Employees T1,
HR.Departments T2
where t1.department_id = t2.department_id(+) and t1.department_id = 50 and t2.department_id <150
代码5-4：
select *
from
HR.Employees T1,
HR.Departments T2
where t1.department_id(+) = t2.department_id and t1.department_id = 50 and t2.department_id <150
代码5-1到代码5-4的Oracle执行计划分析结果相同：

对比：代码5-1到代码5-4的执行计划相同。Oracle是先对T1和T2中数据进行过滤后，再对结果集进行关联查询。且Oracle对表过滤内容进行了优化，对表Departments的查询优化为 TY.Department_ID=50 而不是 TY.Department_ID<150


http://www.cnblogs.com/joyyuan97/archive/2008/09/13/516333.html