gomathi

• Mar 18th, 2006

 

The main difference between procedure and function,function should return a value.procedure need not

Antony Gubert

• Mar 20th, 2006

 

Ref cursor return type can be used when it's required that a procedure / function need to return set of records.

Savithri

• Apr 2nd, 2006

 

Tuning can be taken care by using the Correct Index on the table.We should not use Not equal to, Distinct on the Indexed columns.

Keshav

• May 22nd, 2006

 

SQL Queries ? Best Practices

 

1.         Always use the where clause in your select statement to narrow the number of rows returned.

If we don?t use a where clause, the Oracle performs a full table scan on our table and returns all of the rows.

 

2.         Use EXISTS clause instead of IN clause as it is more efficient than IN and performs faster.

 

Ex:

 

Replace

SELECT * FROM DEPT WHERE DEPTNO IN

 (SELECT DEPTNO FROM EMP E)

With

SELECT * FROM DEPT D WHERE EXISTS

  (SELECT 1 FROM EMP E WHERE D.DEPTNO = E.DEPTNO)

 

Note: IN checks all rows. Only use IN if the table in the sub-query is extremely small.

 

3.         When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause as it is much more efficient than IN.

          Depending on the range of numbers in a BETWEEN, the optimizer will choose to do a full table scan or use the index.

 

4.         Avoid WHERE clauses that are non-sargable. Non-sargable search arguments in the WHERE clause, such as "IS NULL", "OR", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE %500" can prevent the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, or expressions that have the same column on both sides of the operator, are not sargable.

 

Convert multiple OR clauses to UNION ALL.

 

5.         Use equijoins. It is better if you use with indexed column joins.  For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type.

 

6.       Avoid a full-table scan if it is more efficient to get the required rows through an index. It decides full table scan if it has to read more than 5% of the table data (for large tables).

 

7.         Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows and choose selective indexes.

 

8.         Indexes can't be used when Oracle is forced to perform implicit datatype conversion.

 

9.         Choose the join order so you will join fewer rows to tables later in the join order.

l       use smaller table as driving table

l       have first join discard most rows

 

10.      Set up the driving table to be the one containing the filter condition that eliminates the highest percentage of the table.

 

11.      In a where clause (or having clause), constants or bind variables should always be on the right hand side of the operator.

 

12.     Do not use SQL functions in predicate clauses or WHERE clauses or on indexed columns, (e.g. concatenation, substr, decode, rtrim, ltrim etc.) as this prevents the use of the index. Use function based indexes where possible

 

    SELECT * FROM EMP WHERE SUBSTR (ENAME, 1, 3) = ?KES?

 

Use the LIKE function instead of SUBSTR ()

 

13.     If you want the index used, don?t perform an operation on the field.

 

Replace

SELECT * FROM EMPLOYEE WHERE SALARY +1000 = :NEWSALARY

 

With

SELECT * FROM EMPLOYEE WHERE SALARY = :NEWSALARY ?1000

 

14.      All SQL statements will be in mixed lower and lower case.  All reserve words will be capitalized and all user-supplied objects will be lower case. (Standard)

 

15.     Minimize the use of DISTINCT because it forces a sort operation.

 

16.      Try joins rather than sub-queries which result in implicit joins

 

Replace

         SELECT * FROM A WHERE A.CITY IN (SELECT B.CITY FROM B)

 

With

          SELECT A.* FROM A, B WHERE A.CITY = B.CITY

 

17.      Replace Outer Join with Union if both join columns have a unique index:

 

Replace

SELECT A.CITY, B.CITY FROM A, B WHERE A.STATE=B.STATE (+)

 

With

SELECT A.CITY, B.CITY FROM A, B            WHERE A.STATE=B.STATE

UNION

SELECT NULL, B.CITY FROM B WHERE NOT EXISTS

                                                           (SELECT 'X' FROM A.STATE=B.STATE)

 

18.     Use bind variables in queries passed from the application (PL/SQL) so that the same query can be reused. This avoids parsing.

19.      Use Parallel Query and Parallel DML if your system has more than 1 CPU.

  

20.      Match SQL where possible. Applications should use the same SQL statements wherever possible to take advantage of Oracle's Shared SQL Area. The SQL must match exactly to take advantage of this.

 

21.      No matter how many indexes are created, how much optimization is done to queries or how many caches and buffers are tweaked and tuned if the design of a database is faulty, the performance of the overall system suffers. A good application starts with a good design.

 

22.     The following operations always require a sort:

 

          SELECT DISTINCT

          SELECT UNIQUE

          SELECT ....ORDER BY...

          SELECT....GROUP BY...

          CREATE INDEX

          CREATE TABLE.... AS SELECT with primary key specification

          Use of INTERSECT, MINUS, and UNION set operators

          Unindexed table joins

                   Some correlated sub-queries

Poojitha Somareddy

• May 26th, 2006

 

Also the order in which the conditions are given in the 'WHERE' cluase are very important while performing a 'Select' query. The Performance Difference is unnoticed ifother wise the query is run on a Massive Database.

For example for a select statement,

SELECT Emp_id FROM Emp_table WHERE Last_Name = 'Smith' AND Middle_Initial = 'K' AND Gender = 'Female';

The look up for matches in the table is performed by taking the conditions in the WHERE cluase in the reverse order i.e., first all the rows that match the criteria Gender = 'Female' are returned and in these returned rows, the conditon Last_Name = 'Smith' is looked up.

There fore, the order of the conditions in the WHERE clause must be in such a way that the last condition gives minimum collection of potential match rows and the next condition must pass on even little and so on. So, if we fine tune the above query, it should look like,

SELECT Emp_id FROM Emp_table WHERE Gender = 'Female' AND Middle_Initial = 'K' AND Last_Name = 'Smith' ; as Last_Name Smith would return far more less number of rows than Gender = 'Female' as in the former case.

ptmich

 Profile Answers by ptmich 

• May 28th, 2012

 

Ref cursor is used to declare a pointer variable. When you return a ref cursor, you are returning a pointer variable.

Aniruddha Das

• Jun 11th, 2012

 

If you want to take care of performance tuning you need to take care of sub-queries as they takes a lot of time to executing in side the pl-sql blocks.