add

About Me

My photo
Oracle Apps - Techno Functional consultant

Saturday, February 25

Oracle SQL Query Tuning Hints


WHERE Clause
Try to avoid operations on database objects referenced in the WHERE clause.
Given Query
Alternative
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 Query
Alternative
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  Subqueries
Combined  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 Query
Alternative
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.
UNION
UNION 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 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;
     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.

Overview
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.
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 Card=1060)

tkprof gek1_ora_16520.trc gek1_ora_16520.out explain=scott/tiger sort=exeela sys=no

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          2          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          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    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=2 Card=6 Bytes=78)
   3    2       INDEX (RANGE SCAN DESCENDING) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=6 Bytes=102)
tkprof gek1_ora_16521.trc gek1_ora_16521.out explain=scott/tiger sort=exeela sys=no

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.06          2         41          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.06          2         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'),'HH24:MI')
   AND weekday = :b2
   AND t_id = :b3
ORDER BY TO_CHAR(switch_time,'HH24:MI') DESC;

SWITCH_TI     RAT_ID
--------- ----------
01-JAN-98          3
01-JAN-98          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=12)
TKPROF:
tkprof xyz.trc xyz.out explain=user/pwd sort=exeela sys=no
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0         38          8           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0         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'),'HH24:MI');

SWITCH_TI     RAT_ID
--------- ----------
01-JAN-98          3
01-JAN-98          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       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         19          4           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         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 this message:
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 times you would like to join to them anyway. For instance, if the hierarchy table only has surrogate keys, and you would like to display the real value. This tip shows 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, deptno
               FROM emp
               CONNECT BY PRIOR empno = mgr
               START WITH empno = 7839) E
WHERE E.deptno = dept.deptno
/

  EMPLEVEL ENAME                EMPNO     DEPTNO DNAME
---------- --------------- ---------- ---------- --------------
         1 KING                  7839         10 ACCOUNTING
         2   CLARK               7782         10 ACCOUNTING
         3     MILLER            7934         10 ACCOUNTING
         2   JONES               7566         20 RESEARCH
         3     SCOTT             7788         20 RESEARCH
         4       ADAMS           7876         20 RESEARCH
         3     FORD              7902         20 RESEARCH
         4       SMITH           7369         20 RESEARCH
         2   BLAKE               7698         30 SALES
         3     ALLEN             7499         30 SALES
         3     WARD              7521         30 SALES
         3     MARTIN            7654         30 SALES
         3     TURNER            7844         30 SALES
         3     JAMES             7900         30 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 in the from clause, basically does a full query of the table, then returns the values (along with the psuedo-column ROWNUM) to the "outside" query.  The outside query can then operate on the results of the internal query.  In order to access the internal query's columns from the external query, you need to give the internal query an alias ("t1" highlighted below): This allows you to refer to the columns using the "t1" (highlighted below): Since "ROWNUM" is a psuedo-column and therefore a reserved word, you need to alias that column in the internal query in order to refer to it in the outside 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         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
The users intention was most likely to get the the top-five paid people - a top-N query.  What the will get is five random records (the first five we happen to hit), sorted by salary. If you use an inline view with the ORDER BY inside the inline view, you get the correct result.
select * from (select * from emp order by sal desc) where rownum <= 5;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    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 as follows:
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         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          2
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          3
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          4
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          6



Posted by Jithendra Kumar B
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)