oracle的查询优化
一、SQL 语句转换
<一> 试探查询转换
1、视图合并
例子：
简单视图合并
优化前：
create view test_view as
select ename,dname,sal
from emp e,dept d
where e.deptno = d.deptno;
select ename,dname from test_view where sal > 10000;
优化后：
select ename,dname
from emp e,dept d
where e.deptno = d.deptno
and e.sal > 10000;
复杂视图合并
优化前：
create view avg_sal_view as
select deptno,avg(sal) avg_sal_dept
from emp
group by deptno;
select d.name,avg_sal_dept
from dept d,avg_sal_view a
where d.deptno = a.deptno
and d.loc = 'OAKLAND';
优化后：
select d.name,avg(e.sal)
from dept d,emp e
where d.deptno = e.deptno
and d.loc = 'OAKLAND'
group by d.rowid,d.name;
2、子查询展平
Oracle有一些转换能将不同类型的子查询转变为联接、半联接或反联接。子查询展平也是获得良好查询执行性能的基本优化办法。
例子：
优化前：
select d.dname from dept d where d.deptno in (
select e.deptno from emp e where e.sal > 10000);
优化后：
select d.dname
from (select distinct deptno from emp where sal > 10000) e,
dept d
where d.deptno = e.deptno;
3、传递谓词生成
在某些查询中，由于表间的联接关系，一个表中的谓词可以转化为另一个表中的谓词。Oracle 会以这种方式演绎出新的谓词，这类谓词被称为传递谓词。
例子：
优化前：
select count(distinct o_orderkey)
from order,lineitem
where o_orderkey = l_orderkey
and o_orderdate = l_shipdate
and o_orderdate between '1-JAN-2002'
and '31-JAN-2002';
优化后：
select count(distinct o_orderkey)
from order,lineitem
where o_orderkey = l_orderkey
and o_orderdate = l_shipdate
and o_orderdate between '1-JAN-2002'
and '31-JAN-2002'
and l_shipdate between '1-JAN-2002'
and '31-JAN-2002';
4、消除通用子表达
如果同样的子表达或计算在一个查询中出现多次，Oracle 对每一行只评估一次该表达式。
例子：
优化前：
select * from emp,dept
where (emp.deptno = dept.deptno
and loc = 'DALLAS' and sal > 10000)
or (emp.deptno = dept.deptno
and loc = 'DALLAS' and job_title = 'VICE PRESIDENT');
优化后：
select * from emp,dept
where emp.deptno = dept.deptno
and loc = 'DALLAS'
and (sal > 10000 or job_title = 'VICE PRESIDENT');
5、谓词下推和上移
复杂查询往往包含多个视图与子查询，在这些视图和子查询中包含多个谓词。Oracle 可将谓词移入或移出视图，以产生新的高效查询。 谓词下推和上移通过产生新的访问路径以提高执行性能，这在没有增加新的谓词前不可能做到。
例子：
优化前：
CREATE VIEW EMP_AGG AS
SELECT DEPTNO, AVG(SAL) AVG_SAL, FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO, AVG_SAL FROM EMP_AGG WHERE DEPTNO = 10;
优化后：
SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10
GROUP BY DEPTNO;
6、用于"CUBE"查询的分组修整
SQL CUBE 表达式是 SQL group-by 运算符的扩展，使得可以用单个 SQL 语句就可以检索多个集合。对于包含带有 CUBE 表达式的视图的查询，有时可以减少评估该查询所需要的数据量。
例子：
优化前：
SELECT MONTH, REGION, DEPARTMENT
FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE
FROM SALES
GROUP BY CUBE (MONTH, REGION, DEPT))
WHERE MONTH = 'JAN-2001';
优化后：
SELECT MONTH, REGION, DEPARTMENT
FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE
FROM SALES WHERE MONTH = ‘JAN-2001’
GROUP BY MONTH, CUBE(REGION, DEPT))
WHERE MONTH = 'JAN-2001';
7、外联接到内联接的转换在某些情况下，能够确定查询中的一个外联接能产生与内联接相同的结果。在这类情况下，优化程序会将外联接转变为内联接。这种转换让 Oracle 能够进一步合并视图或选用新的联接顺序，查询是一个外联接时就做不到这一点。
<二> 基于开销的查询转换
1、实体化视图重写
以实体化视图的形式预先处理和存储常用数据能够大大加速查询处理。Oracle 能对 SQL 查询进行转换，使查询中对一个或多个表的引用被对一个实体化视图的引用所取代。如果该实体化视图小于原来要引用的表，或比原来要引用的表有更好的访问路径，则该转换后的 SQL 语句比原查询的执行速度会快得多。
Oracle 具有一系列强有力的实体化视图重写技术，允许每个实体化视图能被用在尽可能多的查询类型中。Oracle 实体化视图的另一个显著特点是与 Oracle 数据库中的声明式维集成在一起。Oracle 可以允许生成维的元数据对象，描述维内的层次关系。该层次化元数据用来支持更为复杂的实体化视图查询重写。
注意，使用实体化视图的转换后的查询并不总是比原查询效率高。因为，就算实体化视图比其基于的表小，但这些基础表可以有更好的索引，从而能够被更快地访问。选择优化执行计划的唯一途径是对使用和未使用实体化视图的执行计划进行计算并比较其开销。Oracle 正是这样做的，所以实体化视图重写是基于开销的查询转换范例。
例子：
CREATE MATERIALIZED VIEW SALES_SUMMARY AS
SELECT SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT
FROM SALES, TIME
WHERE SALES.TIME_ID = TIME.TIME_ID
GROUP BY SALES.CUST_ID, TIME.MONTH;
优化前：
SELECT CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) FROM SALES, CUSTOMER, TIME
WHERE SALES.CUST_ID = CUST.CUST_ID
AND SALES.TIME_ID = TIME.TIME_ID
GROUP BY CUSTOMER.CUST_NAME, TIME.MONTH;
优化后：
SELECT CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH,
SALES_SUMMARY.AMT
FROM CUSTOMER, SALES_SUMMARY
WHERE CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID;
2、OR 扩展
该技术把在 WHERE 子句中带有 OR 的查询转换成一个包含多个不带 OR 的查询的 UNION ALL。当 OR 表示的是对不同表的限制条件时，这是大有好处的。
例子：
优化前：
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND (P1.PORT_NAME = 'OAKLAND' OR P2.PORT_NAME = 'OAKLAND') ;
优化后：
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND P1.PORT_NAME = 'OAKLAND'
UNION ALL
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND P2.PORT_NAME = 'OAKLAND'
AND P1.PORT_NAME <> 'OAKLAND';
注意，每个 UNION ALL 分支可以有不同的优化的联接顺序。在第一个分支里，Oracle 可以利用对 P1 的限制，从该表开始联接。而在第二个分支里，Oracle 可以从 P2 开始。产生的计划可能比原来的查询快好几个数量级，这取决于表索引和其数据。这种查询转换必须是基于开销的，因为它并不保证每个查询的性能都会得到提高。
3、星型转换
星型方式是普遍用于数据中心或数据仓库的建模策略。星型模式一般包含一个或多个非常大的表（叫做事实表），用来存储交易数据，另外还包含大量较小的查找表（叫做维表），以来存放描述性数据。
Oracle 支持一种用于评估星型模式查询的技术（叫做“星型转换”）。该技术通过进行转换（向原有的 SQL 中添加新的子查询）来提高星型查询的效率。
这些新的子查询允许利用位图索引更有效地访问事实表。
星型转换是基于开销的查询转换，根据优化程序的开销估算决定是使用某个维的子查询开销较低还是查询重写比原有语句更好。
例子：
优化前：
SELECT STORE.STATE, SUM(SALES.AMOUNT)
FROM SALES, DAY, QUARTER, PRODUCT, STORE
WHERE SALES.DAY_ID = DAY.DAY_ID
AND DAY.QUARTER_ID =QUARTER.QUARTER_ID
AND SALES.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND SALES.STORE_ID = STORE.STORE_ID
AND PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES'
AND QUARTER.QUARTER_NAME = '2001Q3'
GROUP BY STORE.STATE;
优化后：
SELECT STORE.STATE, SUM(SALES.AMOUNT)
FROM SALES, STORE
WHERE SALES.STORE_ID = STORE.STORE_ID
AND SALES.DAY_ID IN (SELECT DAY.DAY_ID
FROM DAY, QUARTER
WHERE DAY.QUARTER_ID = QUARTER.QUARTER_ID
AND QUARTER.QUARTER_NAME = '2001Q3')
AND SALES.PRODUCT_ID IN (SELECT PRODUCT.PRODUCT_ID
FROM PRODUCT
WHERE PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES')
GROUP BY STORE.STATE;
4、外联接视图的谓词下推
通常，当查询包含一个联接到其他表的视图时，该视图可以被合并，以实现更好的查询优化。但是，如果一个视图是用外联接方式联接的，则该视图就不能被合并。在这种情况下，Oracle 有一个特别的谓词下推操作，使联接谓词可以下推到该视图中，该转换使通过该视图中的某个表的索引执行外联接成为可能。这种转换是基于开销的，因为索引访问可能并不是最有效的。
<一> 试探查询转换
1、视图合并
例子：
简单视图合并
优化前：
create view test_view as
select ename,dname,sal
from emp e,dept d
where e.deptno = d.deptno;
select ename,dname from test_view where sal > 10000;
优化后：
select ename,dname
from emp e,dept d
where e.deptno = d.deptno
and e.sal > 10000;
复杂视图合并
优化前：
create view avg_sal_view as
select deptno,avg(sal) avg_sal_dept
from emp
group by deptno;
select d.name,avg_sal_dept
from dept d,avg_sal_view a
where d.deptno = a.deptno
and d.loc = 'OAKLAND';
优化后：
select d.name,avg(e.sal)
from dept d,emp e
where d.deptno = e.deptno
and d.loc = 'OAKLAND'
group by d.rowid,d.name;
2、子查询展平
Oracle有一些转换能将不同类型的子查询转变为联接、半联接或反联接。子查询展平也是获得良好查询执行性能的基本优化办法。
例子：
优化前：
select d.dname from dept d where d.deptno in (
select e.deptno from emp e where e.sal > 10000);
优化后：
select d.dname
from (select distinct deptno from emp where sal > 10000) e,
dept d
where d.deptno = e.deptno;
3、传递谓词生成
在某些查询中，由于表间的联接关系，一个表中的谓词可以转化为另一个表中的谓词。Oracle 会以这种方式演绎出新的谓词，这类谓词被称为传递谓词。
例子：
优化前：
select count(distinct o_orderkey)
from order,lineitem
where o_orderkey = l_orderkey
and o_orderdate = l_shipdate
and o_orderdate between '1-JAN-2002'
and '31-JAN-2002';
优化后：
select count(distinct o_orderkey)
from order,lineitem
where o_orderkey = l_orderkey
and o_orderdate = l_shipdate
and o_orderdate between '1-JAN-2002'
and '31-JAN-2002'
and l_shipdate between '1-JAN-2002'
and '31-JAN-2002';
4、消除通用子表达
如果同样的子表达或计算在一个查询中出现多次，Oracle 对每一行只评估一次该表达式。
例子：
优化前：
select * from emp,dept
where (emp.deptno = dept.deptno
and loc = 'DALLAS' and sal > 10000)
or (emp.deptno = dept.deptno
and loc = 'DALLAS' and job_title = 'VICE PRESIDENT');
优化后：
select * from emp,dept
where emp.deptno = dept.deptno
and loc = 'DALLAS'
and (sal > 10000 or job_title = 'VICE PRESIDENT');
5、谓词下推和上移
复杂查询往往包含多个视图与子查询，在这些视图和子查询中包含多个谓词。Oracle 可将谓词移入或移出视图，以产生新的高效查询。 谓词下推和上移通过产生新的访问路径以提高执行性能，这在没有增加新的谓词前不可能做到。
例子：
优化前：
CREATE VIEW EMP_AGG AS
SELECT DEPTNO, AVG(SAL) AVG_SAL, FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO, AVG_SAL FROM EMP_AGG WHERE DEPTNO = 10;
优化后：
SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10
GROUP BY DEPTNO;
6、用于"CUBE"查询的分组修整
SQL CUBE 表达式是 SQL group-by 运算符的扩展，使得可以用单个 SQL 语句就可以检索多个集合。对于包含带有 CUBE 表达式的视图的查询，有时可以减少评估该查询所需要的数据量。
例子：
优化前：
SELECT MONTH, REGION, DEPARTMENT
FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE
FROM SALES
GROUP BY CUBE (MONTH, REGION, DEPT))
WHERE MONTH = 'JAN-2001';
优化后：
SELECT MONTH, REGION, DEPARTMENT
FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE
FROM SALES WHERE MONTH = ‘JAN-2001’
GROUP BY MONTH, CUBE(REGION, DEPT))
WHERE MONTH = 'JAN-2001';
7、外联接到内联接的转换在某些情况下，能够确定查询中的一个外联接能产生与内联接相同的结果。在这类情况下，优化程序会将外联接转变为内联接。这种转换让 Oracle 能够进一步合并视图或选用新的联接顺序，查询是一个外联接时就做不到这一点。
<二> 基于开销的查询转换
1、实体化视图重写
以实体化视图的形式预先处理和存储常用数据能够大大加速查询处理。Oracle 能对 SQL 查询进行转换，使查询中对一个或多个表的引用被对一个实体化视图的引用所取代。如果该实体化视图小于原来要引用的表，或比原来要引用的表有更好的访问路径，则该转换后的 SQL 语句比原查询的执行速度会快得多。
Oracle 具有一系列强有力的实体化视图重写技术，允许每个实体化视图能被用在尽可能多的查询类型中。Oracle 实体化视图的另一个显著特点是与 Oracle 数据库中的声明式维集成在一起。Oracle 可以允许生成维的元数据对象，描述维内的层次关系。该层次化元数据用来支持更为复杂的实体化视图查询重写。
注意，使用实体化视图的转换后的查询并不总是比原查询效率高。因为，就算实体化视图比其基于的表小，但这些基础表可以有更好的索引，从而能够被更快地访问。选择优化执行计划的唯一途径是对使用和未使用实体化视图的执行计划进行计算并比较其开销。Oracle 正是这样做的，所以实体化视图重写是基于开销的查询转换范例。
例子：
CREATE MATERIALIZED VIEW SALES_SUMMARY AS
SELECT SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT
FROM SALES, TIME
WHERE SALES.TIME_ID = TIME.TIME_ID
GROUP BY SALES.CUST_ID, TIME.MONTH;
优化前：
SELECT CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) FROM SALES, CUSTOMER, TIME
WHERE SALES.CUST_ID = CUST.CUST_ID
AND SALES.TIME_ID = TIME.TIME_ID
GROUP BY CUSTOMER.CUST_NAME, TIME.MONTH;
优化后：
SELECT CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH,
SALES_SUMMARY.AMT
FROM CUSTOMER, SALES_SUMMARY
WHERE CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID;
2、OR 扩展
该技术把在 WHERE 子句中带有 OR 的查询转换成一个包含多个不带 OR 的查询的 UNION ALL。当 OR 表示的是对不同表的限制条件时，这是大有好处的。
例子：
优化前：
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND (P1.PORT_NAME = 'OAKLAND' OR P2.PORT_NAME = 'OAKLAND') ;
优化后：
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND P1.PORT_NAME = 'OAKLAND'
UNION ALL
SELECT * FROM SHIPMENT, PORT P1, PORT P2
WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID
AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND P2.PORT_NAME = 'OAKLAND'
AND P1.PORT_NAME <> 'OAKLAND';
注意，每个 UNION ALL 分支可以有不同的优化的联接顺序。在第一个分支里，Oracle 可以利用对 P1 的限制，从该表开始联接。而在第二个分支里，Oracle 可以从 P2 开始。产生的计划可能比原来的查询快好几个数量级，这取决于表索引和其数据。这种查询转换必须是基于开销的，因为它并不保证每个查询的性能都会得到提高。
3、星型转换
星型方式是普遍用于数据中心或数据仓库的建模策略。星型模式一般包含一个或多个非常大的表（叫做事实表），用来存储交易数据，另外还包含大量较小的查找表（叫做维表），以来存放描述性数据。
Oracle 支持一种用于评估星型模式查询的技术（叫做“星型转换”）。该技术通过进行转换（向原有的 SQL 中添加新的子查询）来提高星型查询的效率。
这些新的子查询允许利用位图索引更有效地访问事实表。
星型转换是基于开销的查询转换，根据优化程序的开销估算决定是使用某个维的子查询开销较低还是查询重写比原有语句更好。
例子：
优化前：
SELECT STORE.STATE, SUM(SALES.AMOUNT)
FROM SALES, DAY, QUARTER, PRODUCT, STORE
WHERE SALES.DAY_ID = DAY.DAY_ID
AND DAY.QUARTER_ID =QUARTER.QUARTER_ID
AND SALES.PRODUCT_ID = PRODUCT.PRODUCT_ID
AND SALES.STORE_ID = STORE.STORE_ID
AND PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES'
AND QUARTER.QUARTER_NAME = '2001Q3'
GROUP BY STORE.STATE;
优化后：
SELECT STORE.STATE, SUM(SALES.AMOUNT)
FROM SALES, STORE
WHERE SALES.STORE_ID = STORE.STORE_ID
AND SALES.DAY_ID IN (SELECT DAY.DAY_ID
FROM DAY, QUARTER
WHERE DAY.QUARTER_ID = QUARTER.QUARTER_ID
AND QUARTER.QUARTER_NAME = '2001Q3')
AND SALES.PRODUCT_ID IN (SELECT PRODUCT.PRODUCT_ID
FROM PRODUCT
WHERE PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES')
GROUP BY STORE.STATE;
4、外联接视图的谓词下推
通常，当查询包含一个联接到其他表的视图时，该视图可以被合并，以实现更好的查询优化。但是，如果一个视图是用外联接方式联接的，则该视图就不能被合并。在这种情况下，Oracle 有一个特别的谓词下推操作，使联接谓词可以下推到该视图中，该转换使通过该视图中的某个表的索引执行外联接成为可能。这种转换是基于开销的，因为索引访问可能并不是最有效的。
