SQL Tips And Tricks Part 3
1) Reference an indexed column in joins when possible. Unindexed columns will result in full table scan unnecessarily.
2) Use proper naming conventions while naming tables and columns. Salary sounds more perfect then slr right?
Example, CREATE TABLE employees (emp_id NUMBER, salary NUMBER);
3) EXISTS clause is faster than IN clause. Avoid using IN clause if you are checking for the availability of record in sub-query.
Example, SELECT SUM (salary) FROM employees WHERE exists (SELECT 1 FROM departments); SELECT SUM (salary) FROM employees WHERE dept_id IN (SELECT dept_id FROM departments);
4) Make use of DISTINCT only when unavoidable. It incurs extra sort operation and slows queries down.
5) PARALLEL hint can be used if the selected data is in large data sets.
6) Only use those columns in the query that are required in the result. Unnecessary use of columns will cause additional I/O operations slowing the performance of the query.
7) Use UNION ALL instead of UNION if possible. UNION clause will use extra sort operation to select common data from queries.
8) It is better to use ANSI joins than standard Oracle joins as it will increase the readability.
9) Use bulk collect instead of conventional looping if the number of rows to be read are large.
10) If possible use implicit cursors rather than explicit cursors.
11) For different tasks make use of different queries. If you plan to use a single query, do it correctly after analyzing the situation.
12) Use clauses in CAP and columns and tables in small letters. Consider doing proper formatting instead of improper space distribution in your code. You will find it more readable and easier to work.
Example, SELECT emp_id, emp_name FROM employees;
13) Do not apply functions such as NVL(), TO_CHAR() or TRUNC on indexed columns. It prevents optimizer to identify the index. Use function-based index if the column is accessed frequently through the index.
14) Aliases of tables should be meaningful.
15) Have the practice to write exceptions in SQL code blocks. It will save a bunch of your time to identify the cause of the error.
16) Equi joins improve the performance of the query. If possible make use of them.
17) Avoid using HINTS unless you are clear about performance improvements.
18) WITH clause can be used for better performance instead of complex sub-queries.
19) Write all hard coded conditions together for a single table in a query. Avoid using them in scattered manner. This will improve the performance of query significantly.
20) Avoid using sort clauses (ORDER BY, GROUP BY) if your requirement is faster data retrieval.
21) Use CASE clause instead of DECODE to improve query readability.
22) CONTINUE clause can be used to jump out of the loop before executing all steps.
23) ROWID, ROWNUM, NEXTVAL and CURRVAL are four pseudo columns in Oracle SQL.
24) RAISE_APPLICATION_ERROR is the procedure of package DBMS_STANDARD.
25) RAISE_APPLICATION_ERROR starts with -20000 and ends at -20999 (thousand user errors).
Example, RAISE_APPLICATION_ERROR (-20102, ‘Hello World! This is OracleMine’);
No comments:
Post a Comment