Oracle查询优化1

Oracle查询优化
SQL 执行的步骤
 解析：安全性检查，语法检查；
 创建：评估多个执行计划，并选择一个最优的执行计划；
 执行：捆绑变量，执行已经创建的执行计划；
 获取：获取结果集，进行转换，排序等
 常见可能导致全表扫描的操作
 使用null条件的查询：where xxx is null;
 对没有索引的字段查询；
 带有like条件的查询：where xxx like ‘%x’；
 带有not equals条件的查询：<> , !=, not in等
 内置函数使索引无效：substr()，to_char()等；
 使用all_rows提示；
 使用parallel 提示；
SQL 调整的目标
 去掉不必要的大表全表扫描；
 缓存小表全表扫描；
 尽量使用主机变量代替直接量，减少SQL语句的解析时间；
 优化索引的使用；
 优化表连接方法；
 优化子查询；
调整SQL子查询
 标准子查询：in 和 exists；
 反连接子查询：not in 和 not exists；
 关联子查询：指在子查询内部引用外部数据表；…from table1 a where … (select…from table2 b Where a.f1 = b.f1…)；对于外部数据集的每一条记录，都将重新执行一次内部子查询；
 非关联子查询：指在子查询内部不会引用外部的数据表；…from table1 a where … (select …from table2 b …)；内部子查询只执行一次；
 调整SQL子查询
 只要可能的话，尽可能的避免使用子查询，而用标准的连接操作来代替，这样可以使用提示来更改执行计划；
 先考虑子查询的合法性，再考虑进行改写；
 使用一个关联子查询时，in与exists子句的子查询的执行计划基本相同；
 在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；
调整SQL子查询
 在内部子查询只有少量的记录时，非关联子查询比关联子查询执行得更快；
 关联子查询使用in子句是多余的；而非关联子查询使用exists子句是不恰当的。
 使用in子句的非关联子查询可以转换为标准连接操作以及使用select distinct来删除重复的记录；
 使用exists子句的关联子查询可以转换为标准连接，但子查询最好只能返回一个记录；
 使用not in子句的非关联子查询可以转sql minus子句，性能相对会高一些；
 使用not in子句的关联子查询，可以使用带有select distinct子句的外部连接操作改写；
ORACLE提示
 http://ymy131931.iteye.com/blog/1998888
常见优化举例
 select num from a where num in (select num from b where b.num/2 > 100)
union
select num from c where num = 10 or num = 20 or num =30;
 MERGE：
 MERGE INTO TABLE_NAME A
 USING TABLE_NAME B
 ON (A.XXX = B.XXX)   /*两表的关联条件*/
 WHEN MATCHED THEN
 UPDATE SET A.XXX=…… （WHERE ……）
 DELETE WHERE ……
 WHEN NOT MATCHED THEN
 INSERT ……;
 如果UPDATE后面跟随着DELETE 则UPDATE 后绝对不能有WHERE语句，否则DELETE 失效
 MERGE
 CREATE TABLE TEAT_A (NUM NUMBER(3),A_ID VARCHAR2 (6));
 CREATE TABLE TEAT_B (NUM NUMBER(3),B_ID VARCHAR2 (6));
 INSERT INTO TEST_A VALUES (1,'001');
 INSERT INTO TEST_A VALUES (2,'001');
 INSERT INTO TEST_A VALUES (3,'001');
 INSERT INTO TEST_A VALUES (3,'002');
 INSERT INTO TEST_A VALUES (4,'001');
 INSERT INTO TEST_A VALUES (5,'002');
 MERGE
 SQL> MERGE INTO TEST_A A
   2  USING (SELECT NUM,B_ID FROM TEST_B) B
   3  ON (A.A_ID = B.B_ID)
   4  WHEN MATCHED THEN
   5  UPDATE SET A.NUM = B.NUM
   6  DELETE WHERE B.B_ID = '002'
   7  WHEN NOT MATCHED THEN
   8  INSERT VALUES
   9  (B.NUM,B.B_ID);
行列转换——列转行
 OARCLE 用户WMSYS下的WM_CONCAT();
例：SELECT WMSYS.WM_CONCAT(NUM) FROM TEST_A;
 CASE WHEN END
例： SELECT JOB ,
  SUM(CASE DEPTNO WHEN 10 THEN SAL END) SAL_10,
  SUM(CASE DEPTNO WHEN 20 THEN SAL END) SAL_20,
  SUM(CASE DEPTNO WHEN 30 THEN SAL END) SAL_30,
  SUM(SAL) HJ
  FROM EMP
  GROUP BY JOB;
 select * from emp pivot(sum(sal) as sum_sal for(DEPTNO) in (10,20,30));
行列转换——列转行
 SELECT * FROM (SELECT SAL, DEPTNO FROM EMP) PIVOT(SUM(SAL) AS PI_SAL FOR DEPTNO IN('10','20','30'));
 SELECT "备件名" AS A,
LISTAGG("仓库名", '--') WITHIN GROUP(ORDER BY "备件名") AS B
FROM TEST_5
GROUP BY "备件名"
行列转行——行转列
SELECT *
FROM VIEW_1 UNPIVOT(A FOR B IN("'10'_PI_SAL",
"'20'_PI_SAL",
"'30'_PI_SAL"));