Oracle 查询优化器 -- 改写查询语句

原创 2015年07月10日 17:17:25

-- Start

当我们执行一条查询语句的时候,我们只告诉 Oracle 我们想要哪些数据,至于数据在哪里,怎么找,那是查询优化器的事情,优化器需要改写查询语句,决定访问路径(如:全表扫描,快速全索引扫描,索引扫描),决定表联接顺序等。至于选择哪种方式,优化器需要根据数据字典做出判断。

那优化器如何改写查询语句呢?

第一种方法叫合并视图,如果你的查询语句中引用了视图,那么优化器会把视图合并到查询中,下面是一个简单的例子,需要注意的是优化器也不是神,如果你的视图包含集合操作符,聚合函数,Group by 等,优化器也傻了,不知道如何合并了。

-- 视图定义
CREATE VIEW employees_50_vw AS
	SELECT employee_id, last_name, job_id, salary, commission_pct, department_id
	FROM employees
	WHERE department_id = 50;

-- 查询语句
SELECT employee_id
FROM employees_50_vw
WHERE employee_id > 150;

-- 合并视图后的查询语句
SELECT employee_id
FROM employees
WHERE department_id = 50
AND employee_id > 150;

第二种方法叫谓词推进(Predicate Pushing),对于那些无法执行合并视图的查询语句,Oracle 会把查询语句中的条件挪到视图中,下面是一个简单的例子。

-- 视图定义
CREATE VIEW all_employees_vw AS
	(SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees)
	UNION
	(SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers);

-- 查询语句的查询语句
SELECT last_name
FROM all_employees_vw
WHERE department_id = 50;

-- 谓词推进
SELECT last_name
FROM ( 
	SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees
		WHERE department_id=50 -- 注意此处
	UNION
	SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers
		WHERE department_id=50 -- 注意此处
);

第三种方法是将非嵌套子查询转化为表连接。下面是一个简单的例子。需要注意的是,并不是所有的非嵌套子查询都能转化为表连接,对于下面的例子而言,如果 customers.cust_id 不是主键,转化后会产生笛卡尔集。

-- 非嵌套子查询
SELECT *
FROM sales
WHERE cust_id IN (SELECT cust_id FROM customers);

-- 表连接
SELECT sales.*
FROM sales, customers
WHERE sales.cust_id = customers.cust_id;

第四种方法是使用物化视图改写查询,物化视图是将一个查询的结果集保存在一个表中,如果你的查询语句和某个物化视图兼容,那么 Oracle 就可以直接从物化视图中取得数据。

--更多参见:Oracle SQL 优化精萃

-- 声明:转载请注明出处

-- Last edited on 2015-07-10

-- Created by ShangBo on 2015-07-10

-- End


oracle千万级数据查询优化

环境:表数据量千万级 需求:组合查询,按条件统计某几个字段取前100条记录 问题:没建索引导致查询结果耗时10多秒,不能忍受。 解决方法: 1.建索引,在哪个字段建?    在这里先提下Oracl...
  • csmnjk
  • csmnjk
  • 2016年10月19日 17:23
  • 5770

oracle 语句提高查询效率的方法

oracle 语句提高查询效率的方法 1:.. where column in(select * from ... where ...); 2:... where exists (select...
  • houpengfei111
  • houpengfei111
  • 2013年07月04日 17:29
  • 9582

oracle提高查询效率的34条方法

转自:http://www.2cto.com/database/201301/181269.html 支持原创 1、选择最有效率的表名顺序   (只在基于规则的优化器中有效):   ORAC...
  • uniqed
  • uniqed
  • 2016年07月22日 08:46
  • 13350

Oracle 优化器

一、优化器基本知识   Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条S...
  • rlhua
  • rlhua
  • 2014年04月28日 21:21
  • 2944

Oracle数据库查询优化方案(处理上百万级记录如何提高处理查询速度)

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索...
  • qq_15766181
  • qq_15766181
  • 2015年08月09日 23:33
  • 6787

Oracle 查询技巧与优化(二) 多表查询

关于Oracle多表查询的一些技巧和优化~
  • wlwlwlwl015
  • wlwlwlwl015
  • 2016年08月05日 08:59
  • 6178

Oracle 查询优化的基本准则详解

Oracle 查询优化的基本准则详解 1:在进行多表关联时,多用 Where 语句把单个表的结果集最小化,多用聚合函数汇总结果集后再与其它表做关联,以使结果集数据量最小化 2:在两张表进行关联...
  • u014421556
  • u014421556
  • 2016年06月28日 21:18
  • 1207

oracle查询优化

oracle 查询优化
  • Little_Red_Rose
  • Little_Red_Rose
  • 2017年11月08日 15:37
  • 96

Oracle 查询优化改写

第一章 单表查询 第二章 给查询结果排序 第三章 使用数字 第一章 单表查询1、将空值转换为实际值 SELECT coalesce(comm,0) FROM emp;说明coalesce与nvl...
  • fengzhuocindy
  • fengzhuocindy
  • 2016年09月17日 11:49
  • 396

Oracle查询优化改写 技巧与案例 .pdf

  • 2016年03月14日 22:19
  • 45.94MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 查询优化器 -- 改写查询语句
举报原因:
原因补充:

(最多只允许输入30个字)