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
Data Types in SQL - Timestamp TIMESTAMP for values representing points in time on a specified uniform scale. DATE is used for timestamps on a scale of one day, such as DATE '2

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

SELECT INTO Statement   The SELECT INTO statement retrieve data from one or more database tables, and then assigns the selected values to the variables or fields. Syntax:

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS

MERGE and TRUNCATE in SQL SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Brief

Redeclaring Predefined Exceptions Keep in mind that, the PL/SQL declares predefined exceptions globally in the package STANDARD; Therefore you need not declare them yourself.

SQLs counterpart of the key words: The text from the opening parenthesis to the end of the fourth line specifies the declared type of the table, meaning that every table ever

User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai