General Sql Rules

General Rules / guidelines for Writing Efficient SQL Statements In order to improve overall application performance, it’s very important to construct SQL queries .in the most efficient way. There can be many different ways to write a SQL query. Here are few tips that can help you in writing efficient and reusable SQL queries. All examples given below 1. Never do calculation or add a built-in function to an index column. This causes an unnecessary full table scan. If it’s a necessity then use a condition with the function. Example : where upper(last_name) = ‘DEEPAK’ And Salary * 3 > 35000; 2. Avoid the use of NOT IN, HAVING and NOT= in where clause, instead, use the NOT EXIST clause. 3. Never specify numeric values in character form and character values in numeric form. This invalidates the indexes and cause a full table scans. 4. Avoid using NULL values in index columns. 5. Avoid using leading WILDCARD with LIKE parameters because trailing such as LIKE ‘%map’ will not use index , cause full table scan. 6. Avoid using sub queries when a JOIN will do the job. 7. Careful while you are creating table in the DATATYPE part and the size of the variable because it has got a great impact on the storage of the table. 8. Careful while creating INDEXES because too much indexes for a table is slowdown the efficiency, in terms of Update and insert statements so be clear which index you need to create on which column. 9. Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary. This is a bad habit that should be stopped. The DISTINCT clause should only be used in SELECT statements if you know that duplicate returned rows are a possibility, and that having duplicate rows in the result set would cause problems with your application. The DISTINCT clause creates a lot of extra work for Database Server, and reduces the physical resources that other SQL statements have at

1

their disposal. Because of this, only use the DISTINCT clause if it is necessary. 10. When using the UNION statement keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like record sets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final record set. If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows. On the other hand, if you know that there will never be any duplicate rows, or if there are, and this presents no problem to your application, then you should use the UNION ALL statement instead of the UNION statement. The advantage of the UNION ALL is that is does not perform the SELECT DISTINCT function, which saves a lot of unnecessary SQL Server resources from being using. 11. The Query Optimizer will perform a table scan or a index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or does not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column in the WHERE clause has a useful index Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead. For example, perhaps it may be more efficient to sort the data at the client than at the server. In other cases, perhaps the client doesn't even need sorted data to achieve its goal. The key here is to remember that you shouldn't automatically sort data, unless you know it is necessary. 12. Avoid using COUNT(*) rather use COUNT(1) or (index_coloumn) or (rowid).

13. If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following: •

Of the search criterions in the WHERE clause, at least one of them should be based on a highly selective column that has an index.

2

•

If at least one of the search criterions in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.

•

If none of the column in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.

14. Avoid using NOT IN , NOT= , NOT LIKE , NOT EXIST in the inner where clause because it will use index rather full table scan. EXAMPLE select map_name,county_code from tblgeoref_map_master a where not exists (select filename from dims_maps b where b.filename = a.map_name). 15. Do not use SELECT * in your queries. Always write the required column names after the SELECT statement, like SELECT CustomerID, CustomerFirstName, City. because parsing is done for all the columns used From all tables specified in FROM clause and This technique results in less disk IO and less network traffic and hence better performance. 16. Avoid using OR cluse, if required use them very carefully. 17. Do not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements. Instead, create stored procedures, and let your applications access these stored procedures. This keeps the data access clean and consistent across all the modules of your application, at the same time centralizing the business logic within the database. So as much as possible keep the SQL / PL/SQL statements in procedures and functions because it’s eliminate Re - parsing. 18. Avoid using NULL in the where condition. 19. Try to put all the SQL /PLSQL statements in the database procedure and function so that Parsing will be eliminated.

20. Order of the tables in Joins: If you specify 2 or more tables in the FROM clause of a SELECT statement, then Oracle parser will process the tables

3

from right to left, so the table name you specify last will be processed first. In this case you have to choose one table as driving table. Always choose the table with less number of records as the driving table. 21. Never compare NULL to anything else: All expressions return NULL if one of the operands is NULL. This is applicable for all operators except Concatenation operator (||). Use Bind Variables: It is also better to use bind variables in queries. That way the query becomes generic and therefore re-usable. For example, instead of writing a query likes SELECT ename, sal FROM emp WHERE deptno = 20; Change it to SELECT ename, sal FROM emp WHERE deptno = :deptno; The first query can be re-used for deptno number 20 only, whereas the second query can be reused for any other deptno also. 22. Use table alias: Always use table alias and prefix all column names with the aliases when you are using more than one table. Use EXISTS instead of DISTINCT: Use EXISTS in place of DISTINCT if you want the result set to contain distinct values while joining tables. For example: SELECT DISTINCT d.deptno, d.dname FROM dept d, emp e WHERE d.deptno = e.deptno; The following SQL statement is a better alternative. 23. SELECT d.deptno, d.dname FROM dept d WHERE EXISTS (SELECT e.deptno FROM emp e WHERE d.deptno = e.deptno); 24. Name the Columns in a Query:There are 2 good reasons why it is better to name the columns in a query rather than to use "select * from ...". 1. Network traffic is reduced. This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column (both of which can be up to 2 GB in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required. 2. It could save the need for changes in the future. If any columns is added to or removed from the base table/view, then “select * “statement can produce wrong results set and statement may fail.

4

Use of expressions and indexes: The optimizer fully evaluates expressions whenever possible and translates certain syntactic constructs into equivalent constructs. This is done either because Oracle can more quickly evaluate the resulting expression than the original expression or because the original expression is merely a syntactic equivalent of the resulting expression. Any computation of constants is performed only once when the statement is optimized rather than each time the statement is executed. Consider these conditions that test for salaries greater than $2000. sal > 24000/12 sal > 2000 sal*12 > 24000 If a SQL statement contains the first condition, the optimizer simplifies it into the second condition. Please note that optimizer does not simplify expressions across comparison operators. The optimizer does not simplify the third expression into the second. For this reason, we should write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns. The Optimizer does not use index for the following statement: SELECT * FROM emp WHERE sal*12 > 24000 ; Instead of this use the following statement: SELECT * FROM emp WHERE sal > 24000/12 ; Use of NOT operator on indexed columns: Never use NOT operator on an indexed column. Whenever Oracle encounters a NOT on an index column, it will perform full-table scan. For Example: SELECT * FROM emp WHERE NOT deptno = 0; Instead use the following: SELECT * FROM emp WHERE deptno > 0; Function or Calculation on indexed columns: Never use a function or calculation on an indexed column. If there is any function is used on an index column, optimizer will not use index. For Example: Do not use until need exactly match string:

5

SELECT * FROM emp WHERE SUBSTR (ename, 1, 3) = 'MIL'; Use following instead: SELECT * FROM emp WHERE ename LIKE 'MIL%'; Do not use the following as || is the concatenate function. Like other functions and it disables index. SELECT * FROM emp WHERE ename || job = 'MILLERCLERK'; Use the following instead SELECT * FROM emp WHERE ename = 'MILLER' AND job = 'CLERK';. Avoid Transformed Columns in the WHERE Clause: Use untransformed column values. For example, use: WHERE a.order_no = b.order_no Rather than WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1)) = TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1)) Combine Multiples Scans with CASE Statements: Often, it is necessary to calculate different aggregates on various sets of tables. Usually, this is done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans can greatly improve performance. Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation. For each aggregation, there could be another column that retrieves the data. The following example has count of all employees who earn less then 2000, between 2000 and 4000, and more than 4000 each month. This can be done with three separate queries. SELECT COUNT (*) FROM emp WHERE sal < 2000; SELECT COUNT (*) FROM emp WHERE sal BETWEEN 2000 AND 4000; SELECT COUNT (*) FROM emp WHERE sal>4000;

However, it is more efficient to run the entire query in a single statement. Each number is calculated as one column. The count uses a filter with the

6

CASE statement to count only the rows where the condition is valid. For example: SELECT COUNT (CASE WHEN sal < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN sal > 4000 THEN 1 ELSE null END) count3 FROM emp;

7