SQL statement OptimizationAvoid using ' * ' 2 in the 1.SELECT clause. Alias for use table when you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each Column. In this way, you can reduce the parsing time and reduce the syntax errors caused by the Column ambiguity. 3. Replace > Efficient with >=: SELECT * from emp where DEPTNO >=4 inefficient: SELECT * from emp where DEPTNO >3 The difference is that the former DBMS will be straight A record that jumps to the first DEPT equals 4 and the latter navigates to the Deptno=3 record and scans forward to the first record with a DEPT greater than 3. 4. Optimize GROUP BY: increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same results but the second one is significantly faster. Inefficient: SELECT JOB, AVG (SAL) from EMPGROUP job has job = ' president ' OR job = ' MANAGER ' Efficient: SELECT JOB, AVG (SAL) from EMPWHERE job = ' president ' OR job = ' MANAGER ' GROUP job Note: To avoid having a HAVING clause, the having will only filter the result set after retrieving all records. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce this overhead.
SQL Statement Optimization Series One
