Top 10 Oracle PL/SQL Performance Tips
1. Avoid NOT EQUAL Operators ‘<>’ and ‘!=”
When these operators are used, the indexes on columns referenced cannot be used. If you need to use the ‘<>’ operator, you can alternatively use the OR clause to distinguish the <> conditions.
2. Avoid ‘IS NULL’ and ‘IS NOT NULL’
The value NULL is undefined, therefore an index on the referenced column will not be used. Creating defaults for NULL values as part of table creation can help with this issue. (i.e.. column VARCHAR2(1) NOT NULL DEFAULT ‘N”)
3. Avoid FUNCTIONS in SQL Where Clause
The optimizer will not use an index when a function is used on an indexed column.
4. Comparing datatypes that are not similar
The Oracle database converts a VARCHAR2 column to a numeric when comparing a string to a number. When this happens, the referenced index is not used. (i.e account_number = 100100).
5. Use the EXISTS function when possible
Utilize the EXISTS function rather than the IN function. The EXIST function looks for a single row match. The IN function has to return all qualifying rows.
6. Use BIND variables
With the use of bind variables, SQL statements can be reused in memory rather than having to be re-parsed with each call.
7. Where are you referencing form fields?
Avoid referencing form fields within the body of a trigger or program unit. Developer 2000 Forms has a PL/SQL engine independent of the database. Occasionally, the engine has to pass SQL off to the database for parsing. To minimize the communication between the client and server, pass form fields via an argument list.
8. Utilize the FORALL statement rather than a FOR LOOP.
When inserting/updating/deleting a large number of rows, you can do the update collectively, rather then one record at a time. This is quicker because only one context switch has to occur between PL/SQL and SQL.
9. Utilize the BULK COLLECT statement
BULK COLLECT works similar to a FORALL statement. It will fetch all rows from the database rather than one row at a time.
10. Using NOCOPY
The PL/SQL engine will pass the parameter by reference rather than by value. This means the values do not have to be copied back and forth. The greatest advantage is seen when passing large records or collections. Can only be used with OUT or IN OUT parameters.
When these operators are used, the indexes on columns referenced cannot be used. If you need to use the ‘<>’ operator, you can alternatively use the OR clause to distinguish the <> conditions.
2. Avoid ‘IS NULL’ and ‘IS NOT NULL’
The value NULL is undefined, therefore an index on the referenced column will not be used. Creating defaults for NULL values as part of table creation can help with this issue. (i.e.. column VARCHAR2(1) NOT NULL DEFAULT ‘N”)
3. Avoid FUNCTIONS in SQL Where Clause
The optimizer will not use an index when a function is used on an indexed column.
4. Comparing datatypes that are not similar
The Oracle database converts a VARCHAR2 column to a numeric when comparing a string to a number. When this happens, the referenced index is not used. (i.e account_number = 100100).
5. Use the EXISTS function when possible
Utilize the EXISTS function rather than the IN function. The EXIST function looks for a single row match. The IN function has to return all qualifying rows.
6. Use BIND variables
With the use of bind variables, SQL statements can be reused in memory rather than having to be re-parsed with each call.
7. Where are you referencing form fields?
Avoid referencing form fields within the body of a trigger or program unit. Developer 2000 Forms has a PL/SQL engine independent of the database. Occasionally, the engine has to pass SQL off to the database for parsing. To minimize the communication between the client and server, pass form fields via an argument list.
8. Utilize the FORALL statement rather than a FOR LOOP.
When inserting/updating/deleting a large number of rows, you can do the update collectively, rather then one record at a time. This is quicker because only one context switch has to occur between PL/SQL and SQL.
9. Utilize the BULK COLLECT statement
BULK COLLECT works similar to a FORALL statement. It will fetch all rows from the database rather than one row at a time.
10. Using NOCOPY
The PL/SQL engine will pass the parameter by reference rather than by value. This means the values do not have to be copied back and forth. The greatest advantage is seen when passing large records or collections. Can only be used with OUT or IN OUT parameters.
No comments:
Post a Comment