SQL Statement to improve performance

SQL queries under workspace can suffer performance issues. Performance can affect due to huge SQL statements. Simpler SQL statements require less processing and generally perform better than complex statements. If you understand the purpose of statement then it is easy to write new statement that meet the requirement. when you write SQL statements you can keep them simple, limit the amount of processing that is required to execute the statement, and ensure better performance from the statements. SQL is very flexible and an equivalent query can be written in many ways, all giving the same result, but with radically different readability and execution response time. Here is a various techniques that you can use to try to optimize your database queries. Query optimization is an important skill for SQL developers and database administrators (DBAs). In order to improve the performance of SQL queries, developers and DBAs need to understand the query optimizer.

The 10 major rules to yield a better response time :

1. Specify Column Names instead of using * in SELECT Query. The SQL query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

2. Try to avoid HAVING clause in SELECT statement. HAVING clause is used to filter the rows after all the rows are selected and is used like a filter.

3. Try to use operators EXISTS, IN, JOINS appropriately in your query.

4. Try to use UNIONALL instead of UNION, whenever possible because UNIONALL does not consider duplicate values in table whereas UNION does.

5. Try to avoid the use of DISTINCT clause.

6. Remove any unnecessary joins from tables

7. Use BETWEEN instead of Relational operator(>,<,=<,=>).

8. Avoid using wildcard (%) at the beginning of a predicates.

9. Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.

10. Use DECODE to avoid the scanning of same rows or joining the same table repetitively.