Thursday, June 24, 2010

examples for query optimization

1.Code the query as simply as possible i.e. no unnecessary columns are selected, no unnecessary GROUP BY or ORDER BY.
It is the same or faster to SELECT by actual column name(s). The larger the table the more likely the savings.
 Use:
 SELECT customer_id, last_name, first_name, street, city FROM customer;
 Rather than,

SELECT * FROM customer;

2.

Given Query

Alternative

SELECT ename, hiredate, sal
FROM emp1
WHERE SUBSTR(ename,1,3) = 'SCO';

SELECT ename, hiredate, sal
FROM emp1
WHERE ename LIKE 'SCO%';

VARIABLE name VARCHAR2(20)
exec name := 'SCOTT'

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

VARIABLE name VARCHAR2(20)
exec name := 'SCOTT'

SELECT ename, hiredate, sal
FROM emp1
WHERE ename LIKE NVL (:name, '%');

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

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

SELECT ename, hiredate, sal
FROM emp1
WHERE ename || empno = 'SCOTT7788';

SELECT ename, hiredate, sal
FROM emp1
WHERE ename = 'SCOTT
 AND empno = 7788;

SELECT ename, hiredate, sal
FROM emp1
WHERE sal + 3000 <>

SELECT ename, hiredate, sal
FROM emp1
WHERE sal <>

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

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

3.

3.

Given Query

Alternative

SELECT d.dname, AVG (e.sal)
FROM emp1 e, dept1 d
WHERE e.deptno = d.deptno
GROUP BY d.dname
 HAVING dname != 'RESEAECH'
AND dname != 'SALES';

SELECT d.dname, AVG (e.sal)
FROM emp1 e, dept1 d
 WHERE e.deptno = d.deptno
AND dname != 'RESEAECH'
AND dname != 'SALES'
GROUP BY d.dname;

4.

Separate Subqueries

Combined Subqueries

SELECT ename
FROM emp1
WHERE sal = (SELECT MAX (sal)
FROM EMP1)
AND comm = (SELECT MAX (comm)
FROM EMP1);

SELECT ename
FROM emp1
WHERE (sal,comm) = (SELECT MAX (sal), MAX(comm)
FROM EMP1);

5. 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 dept1 D,
emp1 E
WHERE D.deptno = E.deptno;

SELECT d.deptno, d.dname
FROM dept1 D
WHERE EXISTS (SELECT 'X'
FROM emp1 E
WHERE E.deptno = D.deptno);

Select deptno,dname from dept where deptno in (select distinct deptno from emp)

6. 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';

7. 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;

Basic

SELECT *
FROM dept1
WHERE deptno NOT IN (SELECT deptno FROM EMP1);

Intermediate

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

Optimized quires

SELECT *
FROM dept1
WHERE NOT EXISTS (SELECT NULL FROM emp1 WHERE emp1.deptno = dept1.deptno);

8. Put reserved words in upper case and application specific identifiers in lower case

IF TO_NUMBER(the_value) > 22
AND Num1 BETWEEN lval AND hval
 THEN
 Newval := 100;
 ELSIF TO_NUMBER(the_value) < 1
 THEN
Calc_tots (TO_DATE ('12-JAN-98'));
 ELSE
Clear_vals;
 END IF;

9. Use whitespace inside a statement

WHILE (total_sales < company_type =" 'NEW')">

10. DECLARE
Min_value NUMBER;
Company_id NUMBER:

Company_name VARCHAR2 (30);
Employee_name VARCHAR2 (60);

Hire_date DATE;
Termination_date DATE;

11. Gen_stats (company_id,
Last_year_date,
Rollup_type,
Total,
Average,
Variance,
Budgeted,
Next_year_plan);

12. Total_sales :=
Product_sales (company_id) +
Service_sales (company_id) admin_cutbacks * .5;

13. SELECT last_name, first_name
FROM employee
WHERE department_id = 15
AND hire_date <>

14. Not like that..

SELECT last_name,
C.name,
MAX (SH.salary) best_salary_ever
FROM employee E,
Company C,
Salary_history SH
WHERE E.company_id = C.company_id
AND E.employee_id = SH.employee_id
AND E.hire_date > ADD_MONTHS (SYSDATE, -60);

15. The readability of code by adding white space

  vFirstName := 'Roger';
  vLastName  := 'Smith';
  vSSN       := 999999999;
 
Not as
  vFirstName := 'Roger';
  vLastName := 'Smith';
  vSSN := 999999999;

**16.use parenthess patterns 

IF (nSSN <>
     
  END IF;

Is much better than

IF nSSN <>
     
  END IF;

17. Align the IN and OUT keywords in columns

formatted

PROCEDURE Days_Between   (dStartDate   IN     date,
                          dEndDate     IN     date,
                          nGPA         IN OUT number,
                          nDaysBetween    OUT number)

un formatted

  PROCEDURE Days_Between (dStartDate IN date,
                          dEndDate IN date,
                          nGPA IN OUT number,
                          nDaysBetween OUT number)

18. When calling a procedure or function, align the parameters into a column. This reduces the visual clutter around the call, making it stand out from the rest of the code.

formatted

DaysBetween (dStartDate => dEnrolledDate,

               dEndDate     => dGraduationDate,
               nGPA         => nFinalGPA,
               nDaysBetween => nDuration);


 un formatted

  DaysBetween (dStartDate => dEnrolledDate,
               dEndDate => dGraduationDate,
               nGPA => nFinalGPA,
               nDaysBetween => nDuration);

19. use mixed case for user-defined identifiers

vString
nBaseSalary
nGPA
iTardyDays
iClassNumber
lComments
rStudentPhoto

20. Code should always be indented consistently

  IF (some expression) THEN
     IF (some expression) THEN
        IF (some expression) THEN
           
        ELSIF (some expression) THEN
           
        END IF;
     END IF;
  END IF;

21. Statements following the WHEN clause of an exception handler should be indented five spaces, in order to create a column-like effect within the exception handler.

 Formatted 

  EXCEPTION
     WHEN OTHERS THEN
          DBMS_Output.Put_Line (SQLERRM);

Unformatted

  EXCEPTION
     WHEN OTHERS THEN
        DBMS_Output.Put_Line (SQLERRM);

22. Spaces should precede and follow character (') literals.Because easy to read and understablity.

  SELECT first_name || ' ' || middle_name || ' ' || last_name
         'student_name'
  FROM   STUDENTS
  WHERE  ssn = 999999999;

23. Do not leave any blank spaces preceding or following the ** operator. 

  nRaisedNum := nNum**nPower;

24. Do not leave blank spaces before or after the plus (+) and minus (-) signs when used as unary operators. 

  nNumber := -nSecondNumber;
  nNumber := +nSecondNumber;
 
25. Identifiers should always use mixed-case and capital letters to indicate separation of elements within an identifier. Thus, a variable of type varchar2 that holds a student’s first name would be    
           vStudentFirstName
            dStartDate
            nEmpnumber

26. The identifiers used for explicitly declared cursors should be meaningful; the suffix _cur should be appended to the identifier. For example: 

CURSOR Students_cur
IS
SELECT first_name, middle_name, last_name, overall_gpa, most_recent_gpa
FROM   STUDENTS;

Identifiers declared using %TYPE should still include a datatype prefix as part of the identifier name: 

nStudentSSN    STUDENTS.ssn%TYPE;

27. Do not add redundant bracketing

IF (x = 1) INCORRECT

IF ((x = 1) AND (y = 2)) INCORRECT

IF x = 1 AND y = 2 CORRECT

28. Use meaningful abbreviations for table and column aliases

SELECT cols

FROM employees emp

, companies com

, profiles pro

, sales sal

WHERE com.com_id = emp.emp_com_id

AND pro.pro_com_id = com.com_id

AND sal.sal_com_id (+)= pro.pro_com_id

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)