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
