Oracle SQL Query Tuning Hints

http://blog.csdn.net/venus0314/article/details/510770

最近才知道 query tuning 是什么了。说白了就是查询优化。网上找到的,先收藏再说,以后慢慢研究。

Oracle SQL Query Tuning Hints


WHERE Clause

Try to avoid operations on database objects referenced in the WHERE clause.

Given QueryAlternative

SELECT ename, hiredate, sal
FROM emp
WHERE SUBSTR(ename,1,3) = ‘SCO’;

SELECT ename, hiredate, sal
FROM emp
WHERE ename LIKE ‘SCO%’;

VARIABLE name VARCHAR2(20)
exec name := ‘SCOTT’

SELECT ename, hiredate, sal
FROM emp
WHERE ename = NVL (:name, ename);

VARIABLE name VARCHAR2(20)
exec name := ‘SCOTT’

SELECT ename, hiredate, sal
FROM emp
WHERE ename LIKE NVL (:name, ‘%’);

SELECT ename, hiredate, sal
FROM emp
WHERE TRUNC (hiredate) = TRUNC (SYSDATE);

SELECT ename, hiredate, sal
FROM emp
WHERE hiredate BETWEEN TRUNC (SYSDATE)
  AND TRUNC (SYSDATE) + .99999;

SELECT ename, hiredate, sal
FROM emp
WHERE ename || empno = ‘SCOTT7788′;

SELECT ename, hiredate, sal
FROM emp
WHERE ename = ‘SCOTT
AND empno = 7788;

SELECT ename, hiredate, sal
FROM emp
WHERE sal + 3000 < 5000;

SELECT ename, hiredate, sal
FROM emp
WHERE sal < 2000;

SELECT ename, hiredate, sal
FROM emp
WHERE sal != 0;

SELECT ename, hiredate, sal
FROM emp
WHERE sal > 0;

HAVING Clause

The HAVING clause filters selected rows only after all rows have been fetched.  Using a WHERE clause helps reduce overheads in sorting, summing, etc.  HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

Given QueryAlternative

SELECT d.dname, AVG (e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
HAVING dname != ‘RESEAECH’
AND dname != ‘SALES’;

SELECT d.dname, AVG (e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND dname != ‘RESEAECH’
AND dname != ‘SALES’
GROUP BY d.dname;

Combined  Subqueries

Minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SELECTs or multicolumn UPDATEs.

Separate  SubqueriesCombined  Subqueries

SELECT ename
FROM emp
WHERE sal = (SELECT MAX (sal)
FROM lookup)
AND comm = (SELECT MAX (comm)
FROM lookup);

SELECT ename
FROM emp
WHERE (sal,comm) = (SELECT MAX (sal),
MAX(comm)
FROM lookup);

EXISTS, NOT IN, Table Joins

Consider the alternatives EXISTS, IN and table joins when doing multiple table joins. None of these are consistently faster; it depends on your data.

SELECT ename
FROM emp E
WHERE EXISTS (SELECT ‘X’
FROM dept
WHERE deptno = E.deptno
AND dname = ‘ACCOUNTING’);

SELECT ename
FROM emp E
WHERE deptno IN (SELECT deptno
FROM dept
WHERE deptno = E.deptno
AND dname = ‘ACCOUNTING’);

SELECT ename
FROM dept D, emp E
WHERE E.deptno = D.deptno
AND D.dname = ‘ACCOUNTING’;

DISTINCT

Avoid joins that require the DISTINCT qualifier on the SELECT list in queries which are used to determine information at the owner end of a one-to-many relationship.  The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer realizes when the subquery has been satisfied once, there is no need to proceed further and the next matching row can be fetched.

Given QueryAlternative

SELECT DISTINCT d.deptno, d.dname
FROM dept D,
emp E
WHERE D.deptno = E.deptno;

SELECT d.deptno, d.dname
FROM dept D
WHERE EXISTS (SELECT ‘X’
FROM emp E
WHERE E.deptno = D.deptno);

UNION ALL

Consider whether a UNION ALL will suffice in place of a UNION. The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned.  A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter.  If your tables are mutually exclusive (include no duplicate records), or you don’t care if duplicates are returned, the UNION ALL is much more efficient.

UNIONUNION ALL

SELECT acct, balance
FROM debit
WHERE trandate = ’31-DEC-95′
UNION
SELECT acct, balance
FROM credit
WHERE trandate = ’31-DEC-95′;

SELECT acct, balance
FROM debit
WHERE trandate = ’31-DEC-95′
UNION ALL
SELECT acct, balance
FROM credit
WHERE trandate = ’31-DEC-95′;

DECODE

Consider using DECODE to avoid having to scan the same rows repetitively or join the same table repetitively. Note, DECODE is not necessarily faster as it depends on your data and the complexity of the resulting query. Also, using DECODE requires you to change your code when new values are allowed in the field.

SELECT COUNT(*)
FROM emp
WHERE status = ‘Y’
AND ename LIKE ‘SMITH%’;
———-
SELECT COUNT(*)
FROM emp
WHERE status = ‘N’
AND ename LIKE ‘SMITH%’;

SELECT COUNT(DECODE(status, ‘Y’, ‘X’, NULL)) Y_count,
COUNT(DECODE(status, ‘N’, ‘X’, NULL)) N_count
FROM emp
WHERE ename LIKE ‘SMITH%’;

Anti Joins

An anti-join is used to return rows from a table that that are present in another table. It might be used for example between DEPT and EMP to return only those rows in DEPT that didn’t join to anything in EMP;

SELECT *
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM EMP);

SELECT dept.*
FROM dept, emp
WHERE dept.deptno = emp.deptno (+)
   AND emp.ROWID IS NULL;

SELECT *
FROM dept
WHERE NOT EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno);

Full Outer Joins

Normally, an outer join of table A to table B would return every record in table A, and if it had a mate in table B, that would be returned as well. Every row in table A would be output, but some rows of table B might not appear in the result set. A full outer join would return ebery row in table A, as well as every row in table B. The syntax for a full outer join is new in Oracle 9i, but it is a syntactic convenience, it is possible to produce full outer joins sets using conventional SQL.

update emp set deptno = 9 where deptno = 10;
commit;

Conventional SQLNew Syntax

SELECT empno, ename, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno(+) = dept.deptno
UNION ALL
SELECT empno, ename, emp.deptno, NULL
FROM emp, dept
WHERE emp.deptno = dept.deptno(+)
AND dept.deptno IS NULL
ORDER BY 1,2,3,4;

EMPNO ENAME          DEPTNO DNAME
———- ———- ———- ————–
7369 SMITH              20 RESEARCH
7499 ALLEN              30 SALES
7521 WARD               30 SALES
7566 JONES              20 RESEARCH
7654 MARTIN             30 SALES
7698 BLAKE              30 SALES
7782 CLARK               9
7788 SCOTT              20 RESEARCH
7839 KING                9
7844 TURNER             30 SALES
7876 ADAMS              20 RESEARCH
7900 JAMES              30 SALES
7902 FORD               20 RESEARCH
7934 MILLER              9
10 ACCOUNTING
40 OPERATIONS

SELECT empno, ename,
NVL(dept.deptno,emp.deptno) deptno, dname
FROM emp FULL OUTER JOIN dept ON
(emp.deptno = dept.deptno)
ORDER BY 1,2,3,4;

EMPNO ENAME          DEPTNO DNAME
———- ———- ———- ————–
7369 SMITH              20 RESEARCH
7499 ALLEN              30 SALES
7521 WARD               30 SALES
7566 JONES              20 RESEARCH
7654 MARTIN             30 SALES
7698 BLAKE              30 SALES
7782 CLARK               9
7788 SCOTT              20 RESEARCH
7839 KING                9
7844 TURNER             30 SALES
7876 ADAMS              20 RESEARCH
7900 JAMES              30 SALES
7902 FORD               20 RESEARCH
7934 MILLER              9
10 ACCOUNTING
40 OPERATIONS

Inline VIEWS

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

OK, so why use the complicated select in the first place?  Why not just create the view? Well, one good reason is that creating a view gives you another database object to maintain, and adds more complexity to your system.  By placing the view “inside” your main select, you have all of the code needed to support the query in one place.

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>