Avoid the not null constraint - performance of application, PL-SQL Programming

Avoid the NOT NULL Constraint

In the PL/SQL, using the NOT NULL constraint incur a performance cost. Consider the illustration as shown below:

PROCEDURE calc_m IS

m NUMBER NOT NULL;

a NUMBER;

b NUMBER;

BEGIN

...

m := a + b;

As m is constrained by the NOT NULL, the value of the expression a + b is assigned to a temporary variable, that is then tested for the nullity. If the variable is not null, its value is assigned to m. Or else, an exception is raised. Though, if m were not constrained, then the value would be assigned to m directly. A more accurate way to write the last illustration shown below:

PROCEDURE calc_m IS

m NUMBER; -- no constraint

a NUMBER;

b NUMBER;

BEGIN

...

m := a + b;

IF m IS NULL THEN ... -- enforce constraint programmatically

END;

Posted Date: 10/5/2012 5:01:03 AM | Location : United States







Related Discussions:- Avoid the not null constraint - performance of application, Assignment Help, Ask Question on Avoid the not null constraint - performance of application, Get Answer, Expert's Help, Avoid the not null constraint - performance of application Discussions

Write discussion on Avoid the not null constraint - performance of application
Your posts are moderated
Related Questions
EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

RETURN Statement The RETURN statement instantly completes the execution of a subprogram and returns control to the caller. The Execution then resumes with the statement below t

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Records Records are the items of the type RECORD. The Records have exclusively named fields that can store the data values of various types. And hence, a record treat associate

Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

heap sort program in pl/sql

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a