Performance Hindrance

USAGE OF BIND VARIABLES


You can also assign variables within PL/SQL. The nice advantage of PL/SQL is that just by using variables in PL/SQL, they are automatically bind variables, so there is no special coding required. And, unlike SQL Plus, no colon is required when referencing a variable that was defined within the PL/SQL block

INLINE VIEWS - PERFORMANCE CONSIDERATION


Inline views are created and used only for the duration of a query, their results are held in the program global memory area, and if too large, the temporary tablespace. Before using an inline view, the following questions should be considered:

*    Most importantly, how often will the SQL containing the inline view be run? (If only once or rarely, then it might be best to simply execute the query and not worry about any potential performance impact).
*    How many rows will be contained in the inline view?
*    What will the row length be for the inline view?
*    How much memory is allocated for the pga_aggregate_target or memory_target setting?
*    How big is the temporary tablespace that is used by your Oracle user or schema?

If you have a simple ad hoc query you are doing, this kind of analysis may not be necessary. If you are creating a SQL statement that will run in a production environment, it is important to perform this analysis, as if all the temporary tablespace is consumed by an inline view, it affects not only the completion of that query, but also the successful completion of any processing for any user that may use that specific temporary tablespace.

Even the Queries with a "WITH" Clause is an inline view which makes use of the temporary tablespace or the PGA (Program Global Area) for Storing the results of the Query.

USE OF "NOT" CLAUSE WILL NEGATE THE USE OF INDEXES


You can effectively use the NOT clause several ways:

*    Comparison operators ('<>', '!=', '^=')
*    NOT IN
*    NOT LIKE