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

Ask an Expert

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

Write your message here..

Related Questions

Exit statement - syntax, EXIT Statement   You can use the EXIT stateme... EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you

Providing results of queries, Providing Results of Queries Expressing ... Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

Using not null-declarations in sql, Using NOT NULL Besides assigning an ... Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Explicit cursor attributes, Explicit Cursor Attributes The cursor varia... Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Opening a cursor variable, Opening a Cursor Variable The OPEN-FOR stat... Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Pl sql code review, PL SQL Code Review HEADER ELEMENTS File Name ... PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh

Pl/sql expressions , Pl/SQL Expressions The Expressions are constructed... Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value

Cursor attributes in pl sql, Cursor Attributes   The Cursors and curso... Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

Aggregate operators sql, Aggregate Operators SQL Supports all of the a... Aggregate Operators SQL Supports all of the aggregate operators mentioned in the theory book and many more besides. The syntax, however, involves an unusual trick that SQL cal

Assignment 4, I need a query for PL/SQL, selecting names with cursor, goes ... I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user