Oracle SQL Best Practices

1. Replace DISTINCT with EXISTS to eliminate sort operation

2. If you filter data in group by, specifying conditions in the WHERE clause has better performance than HAVING clause. Because data has been filtered out before the GROUP, fewer rows are summarized.

3. UNION will execute an SORT for the result set of the two SELECT statements and eliminate duplicate rows. The cost will be high, while union all will not. Therefore, if the application can process duplicates or is sure that there are no duplicate records, use union all instead of UNION.

4. Don't use it without UNION.

5. to avoid confusion between left join and right join in an SQL statement, a consistent field of view should be used. For example, only FULL or LEFT OUTER JOIN is used for OUTER JOIN and RIGHT OUTER JOIN is ignored.

For example:

SQL code

SELECT e. lname, j. function, d. name

FROM job j left outer join employee e ON e. job_id = j. job_id

Right outer join department d ON e. dept_id = d. dept_id;

SELECT e. lname, j. function, d. name

FROM job j left outer join employee e ON e. job_id = j. job_id

Right outer join department d ON e. dept_id = d. dept_id;

　　

It should be converted:

SQL code

SELECT e. lname, j. function, d. name

FROM department d LEFT OUTER JOIN

(Job j left outer join employee e

ON e. job_id = j. job_id)

ON e. dept_id = d. dept_id;

SELECT e. lname, j. function, d. name

FROM department d LEFT OUTER JOIN

(Job j left outer join employee e

ON e. job_id = j. job_id)

ON e. dept_id = d. dept_id;

1 2 Next Page