Oracle SQL Query Tuning Hints WHERE Clause Try to avoid operations on database objects referenced in the WHERE clause.
Given Query
Alterna
SELECT ename, hiredate, sal FROM emp WHERE SUBSTR(ename,1,3) = 'SCO';
SELECT ename, hiredate, sa FROM emp WHERE ename LIKE 'SCO%';
VARIABLE name VARCHAR2(20) exec name := 'SCOTT'
VARIABLE name VARCHAR2(20) exec name := 'SCOTT'
SELECT ename, hiredate, sal FROM emp WHERE ename = NVL (:name, ename);
SELECT ename, hiredate, sa FROM emp WHERE ename LIKE NVL (:nam
SELECT ename, hiredate, sal FROM emp WHERE TRUNC(hiredate) = TRUNC (SYSDATE);
SELECT ename, hiredate, sa FROM emp WHERE hiredate BETWEENTRU
SELECT ename, hiredate, sal FROM emp WHERE ename || empno = 'SCOTT7788';
SELECT ename, hiredate, sa FROM emp WHERE ename = 'SCOTT
SELECT ename, hiredate, sal FROM emp WHERE sal + 3000 < 5000;
SELECT ename, hiredate, sa FROM emp WHERE sal < 2000;
SELECT ename, hiredate, sal FROM emp WHERE sal !=0;
SELECT ename, hiredate, sa FROM emp WHERE sal >0;
AND TRUNC (SYSDATE) + .9
AND empno = 7788;
HAVING Clause The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause summing, etc. HAVING clauses should only be used when columns with summary operations applied t clause.
Given Query 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';
Alternat 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 inclu
multicolumn UPDATEs.
Combined Subq
Separate 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(com FROM lookup);
EXISTS, NOT IN, Table Joins Consider the alternatives EXISTS, IN and table joins when doing multiple table joins. None of th 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 d end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satis and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer reali satisfied once, there is no need to proceed further and the next matching row can be fetched.
Given Query SELECT DISTINCT d.deptno, d.dname FROM dept D, emp E WHERE D.deptno = E.deptno;
Alternative SELECT d.deptno,d.dname FROM dept D WHERE EXISTS (SELECT 'X' FROM emp E WHERE E.deptno = D.
ION ALL Consider whether a UNION ALL will suffice in place of a UNION. The UNION clause forces all row UNION to be sorted and merged and duplicates to be filtered before the first row is returned. A
including duplicates and does not have to perform any sort, merge or filter. If your tables are m duplicate records), or you don't care if duplicates are returned, the UNION ALL is much more eff
UNION SELECT acct, balance FROM debit WHERE trandate = '31-DEC-95'
UNION
SELECT acct, balance FROM credit WHERE trandate = '31-DEC-95';
UNION 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 tabl not necessarily faster as it depends on your data and the complexity of the resulting query. Also 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 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);
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 i as well. Every row in table A would be output, but some rows of table B might not appear in the result 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, it is possible to produce full outer joins sets using conventional SQL. update emp set deptno = 9 where deptno = 10; commit;
Conventional SQL
New 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;
SELECT empno, ename, NVL(dept.deptno,emp.deptno) 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 9 7934 MILLER 10 ACCOUNTING 40 OPERATIONS
EMPNO ENAME ---------- ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER
DEPTNO D 20 30 30 20 30 30 9 20 9 30 20 30 20 9 10 40
R S S R S S R S R S R A O
VIEWS The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just OK, so why use the complicated select in the first place? Why not just create the view? Well, one good gives you another database object to maintain, and adds more complexity to your system. By placing you have all of the code needed to support the query in one place.
The Power of Inline Views
Overview The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just OK, so why use the complicated select in the first place? Why not just create the view? Well, one good gives you another database object to maintain, and adds more complexity to your system. By placing you have all of the code needed to support the query in one place. If you have a query as the following ...
SELECT a FROM table WHERE id = :id AND b = (SELECT MAX (b) FROM table WHERE id = :id) ... it can be worth to check if an inline view, instead of the subquery will be faster.
Example 1 (Replace Subquery for MAX) With Subquery CREATE TABLE test (id INT, height INT, acc_date DATE); INSERT INTO test (id, height, acc_date) SELECT MOD(ROWNUM,1000), DBMS_RANDOM.RANDOM, SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000) FROM all_objects; 6357 rows created. COMMIT; CREATE INDEX test_idx on test (id, acc_date, height); Index created. ANALYZE TABLE test COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS; Table analyzed.
alter session set timed_statistics=true; alter session set sql_trace=true; VARIABLE b1 NUMBER exec :b1 := 10
ALTER SESSION SET TIMED_STATISTICS=TRUE; ALTER SESSION SET SQL_TRACE=TRUE; SELECT max(height) from test WHERE id = :b1 AND acc_date = (SELECT MAX(acc_date) FROM test WHERE id = :b1); MAX(HEIGHT) ----------1480603530 Elapsed: 00:00:00.12 Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=1 Bytes=17) 3 2 SORT (AGGREGATE) 4 3 FIRST ROW (Cost=2 Card=6 Bytes=60) 5 4 INDEX (RANGE SCAN (MIN/MAX)) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Car
tkprof gek1_ora_16520.trc gek1_ora_16520.out explain=scott/tiger sort=exeela sys=no call count cpu elapsed ------- ------ -------- ---------- ---------- ---------- ---------Parse 1 0.00 0.00 Execute 1 0.00 0.00 Fetch 2 0.00 0.00 ------- ------ -------- ---------- ---------- ---------- ---------total 4 0.00 0.00
disk ---------0 0 0 ---------0
query
current
rows
0 2 2
0 0 0
0 0 1
4
0
1
With Inline View VARIABLE b1 NUMBER exec :b1 := 10 SELECT height FROM (SELECT height FROM test WHERE id = :b1 ORDER BY id DESC, acc_date DESC, height DESC) WHERE ROWNUM = 1; HEIGHT ---------1480603530 Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=13)
1 2 3
0 1 2
COUNT (STOPKEY) VIEW (Cost=2 Card=6 Bytes=78) INDEX (RANGE SCAN DESCENDING) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=6
tkprof gek1_ora_16521.trc gek1_ora_16521.out explain=scott/tiger sort=exeela sys=no call count cpu elapsed ------- ------ -------- ---------- ---------- ---------- ---------Parse 1 0.03 0.06 Execute 1 0.00 0.00 Fetch 2 0.00 0.00 ------- ------ -------- ---------- ---------- ---------- ---------total 4 0.03 0.06
disk ---------2 0 0 ---------2
query
current
rows
41 0 2
0 0 0
0 0 1
43
0
1
Example 2 (Replace Subquery for MAX) Original Query from a trace session: SELECT switch_time,rat_id FROM tariff WHERE effdate = (SELECT MAX(effdate) FROM tariff WHERE effdate <= TRUNC(:b1) AND weekday = :b2 AND t_id = :b3) AND TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(:b1,'HH24:MI') AND weekday = :b2 AND t_id = :b3 ORDER BY TO_CHAR(switch_time,'HH24:MI') DESC
With Subquery alter session set timed_statistics=true; select value from v$parameter where name = 'user_dump_dest'; alter session set sql_trace=true;
VARIABLE b1 VARCHAR2(19) exec :b1 := '07.04.1999:13:30:31' VARIABLE b2 NUMBER exec :b2 := 2 VARIABLE b3 NUMBER exec :b3 := 317 SELECT switch_time, rat_id FROM tariff WHERE effdate = (SELECT MAX(effdate) FROM tariff WHERE effdate <= TRUNC(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS')) AND weekday = :b2 AND T_ID = :b3) AND TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(TO_DATE(:b1,'DD.MM. YYYY:HH24:MI:SS'),'
AND weekday = :b2 AND t_id = :b3 ORDER BY TO_CHAR(switch_time,'HH24:MI') DESC; SWITCH_TI --------- ---------01-JAN-98 01-JAN-98
RAT_ID 3 1
Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=21) 1 0 SORT (ORDER BY) (Cost=4 Card=1 Bytes=21) 2 1 FILTER 3 2 TABLE ACCESS (FULL) OF 'TARIFF' (Cost=2 Card=1 Bytes=21) 4 3 SORT (AGGREGATE) 5 4 FILTER 6 5 INDEX (RANGE SCAN) OF 'PK_TARIFF' (UNIQUE) (Cost=2 Card=1 Bytes=1
TKPROF:
tkprof xyz.trc xyz.out explain=user/pwd sort=exeela sys=no call count cpu elapsed ------- ------ -------- ---------- ---------- ---------- ---------Parse 1 0.00 0.00 Execute 1 0.00 0.00 Fetch 2 0.01 0.00 ------- ------ -------- ---------- ---------- ---------- ---------total 4 0.01 0.00
disk ---------0 0 0 ---------0
query
current
rows
0 0 38
0 0 8
0 0 4
38
8
4
Misses in library cache during parse: 0 Optimizer goal: CHOOSE
With Inline View VARIABLE b1 VARCHAR2(19) exec :b1 := '07.04.2005:13:30:31' VARIABLE b2 NUMBER exec :b2 := 2 VARIABLE b3 NUMBER exec :b3 := 317 SELECT switch_time, rat_iD FROM (SELECT switch_time, rat_id FROM tariff WHERE effdate <= TRUNC(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS')) AND weekday = :b2 AND t_id = :b3 ORDER BY effdate DESC) WHERE TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(TO_DATE(:b1,'DD.MM. YYYY:HH24:MI:SS'),'
SWITCH_TI --------- ---------01-JAN-98 01-JAN-98
RAT_ID 3 1
Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=22) 1 0 VIEW (Cost=4 Card=1 Bytes=22) 2 1 SORT (ORDER BY) (Cost=4 Card=1 Bytes=21) 3 2 FILTER 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TARIFF' (Cost=2 Card=1 Bytes=21) 5 4 INDEX (RANGE SCAN) OF 'PK_TARIFF' (UNIQUE) (Cost=2 Card=1)
TKPROF:
tkprof xyz.trc xyz.out explain=user/pwd sort=exeela sys=no call count cpu elapsed ------- ------ -------- ---------- ---------- ---------- ---------Parse 1 0.00 0.00 Execute 1 0.00 0.00 Fetch 2 0.00 0.00 ------- ------ -------- ---------- ---------- ---------- ---------total 4 0.00 0.00
disk ---------0 0 0 ---------0
query
current
rows
0 0 19
0 0 4
0 0 4
19
4
4
Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Example 3 (cannot have join with CONNECT BY) Have you ever tried to join to a hierarchical query (a query using CONNECT BY and PRIOR) only to get ORA-01437: cannot have join with CONNECT BY
One of the limitations of hierarchical queries is that you cannot join to them. However, there are often t them anyway. For instance, if the hierarchy table only has surrogate keys, and you would like to displa how you can use "Inline Views" to join tables to a hierarchical query. SELECT level, LPAD(' ',2*level-2)||ename ename, empno, mgr, dept.deptno, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno CONNECT BY PRIOr empno = mgr START WITH empno = 7839; ORA-01437: cannot have join with CONNECT BY SELECT E.emplevel, SUBSTR(E.ename,1,15) "ENAME", E.empno, dept.deptno, dept.dname FROM dept, (SELECT level emplevel, LPAD(' ',2*level-2)||ename ename, empno, mgr, dep
FROM emp CONNECT BY PRIOR empno = mgr START WITH empno = 7839) E
WHERE E.deptno = dept.deptno / EMPLEVEL ENAME EMPNO ---------- --------------- ---------- ---------- -------------1 KING 7839 2 CLARK 7782 3 MILLER 7934 2 JONES 7566 3 SCOTT 7788 4 ADAMS 7876 3 FORD 7902 4 SMITH 7369 2 BLAKE 7698 3 ALLEN 7499 3 WARD 7521 3 MARTIN 7654 3 TURNER 7844 3 JAMES 7900
DEPTNO DNAME 10 10 10 20 20 20 20 20 30 30 30 30 30 30
ACCOUNTING ACCOUNTING ACCOUNTING RESEARCH RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES SALES SALES SALES SALES
Example 3 (ROWNUM 1 Problem) A rownum restriction starting with 1 works: ROWNUM does not work for ranges that don't start at 1. A ROWNUM restriction starting with 1 works: SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN /
1 and 3
ROWNUM ENAME ---------- ---------1 SMITH 2 ALLEN 3 WARD
However, if you try to use a range it will not work. For example: SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN /
2 and 3
no rows selected
Using an Inline View to get around this limitation: SELECT t1.rn, t1.ename FROM (SELECT ROWNUM rn, ename FROM emp) t1 WHERE t1.rn BETWEEN 2 and 3 /
The main trick to this query is the "internal" select statement. This select statement i
table, then returns the values (along with the psuedo-column ROWNUM) to the "outside" query. The o the results of the internal query. In order to access the internal query's columns from the external que query an alias ("t1" highlighted below): This allows you to refer to the columns using the "t1" (highligh psuedo-column and therefore a reserved word, you need to alias that column in the internal query in o query:
Example 4 (ROWNUM and ORDER BY Problem, TOP-N Queries The following query form is almost wrong: select * from emp where ROWNUM <= 5 order by sal desc;
/* WRONG! */
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7566 JONES MANAGER 7839 02-APR-81 7499 ALLEN SALESMAN 7698 20-FEB-81 7521 WARD SALESMAN 7698 22-FEB-81 7654 MARTIN SALESMAN 7698 28-SEP-81 7369 SMITH CLERK 7902 17-DEC-80
SAL 2975 1600 1250 1250 800
COMM 300 500 1400
DEPTNO 20 30 30 30 20
The users intention was most likely to get the the top-five paid people - a top-N query. What the will g five we happen to hit), sorted by salary. If you use an inline view with the ORDER BY inside the inline v select * from
(select * from emp order by sal desc) where rownum <= 5;
EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7839 KING PRESIDENT 17-NOV-81 7788 SCOTT ANALYST 7566 09-DEC-82 7902 FORD ANALYST 7566 03-DEC-81 7566 JONES MANAGER 7839 02-APR-81 7698 BLAKE MANAGER 7839 01-MAY-81
SAL
COMM
5000 3000 3000 2975 2850
DEPTNO 10 20 20 20 30
Example 5 (Pagination with ROWNUM) Pagination with ROWNUM can be used to get rows N thru M of a result set. The general form of this is a
SELECT * FROM (SELECT a.*, ROWNUM rn FROM (enter your query here) a WHERE ROWNUM <= :MAX_ROW) WHERE rn >= :MIN_ROW; SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp) a WHERE ROWNUM <= 6) WHERE rn >= 2; EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -
SAL
COMM
DEPTNO
7499 7521 7566 7654 7698
ALLEN WARD JONES MARTIN BLAKE
SALESMAN SALESMAN MANAGER SALESMAN MANAGER
7698 7698 7839 7698 7839
20-FEB-81 22-FEB-81 02-APR-81 28-SEP-81 01-MAY-81
1600 1250 2975 1250 2850
300 500 1400
30 30 20 30 30
