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
set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I

Example of GROUP BY and COLLECT Operator Example: Using GROUP BY and COLLECT to obtain C_ER2 SELECT CourseId, CAST ( COLLECT (ROW (StudentId, Mark)) AS ROW (Studen

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

I need SQL , WP SQL Expert Project Description: Expert required to modify WP SQL query. Skills required are MySQL, SQL, PHP, Wordpress

a. Create a table odetails_new. It has all the attributes of odetails and an additional column called cost, whose values are the product of the quantity and price of the part bein

Quantification in SQL To quantify something, as the theory book has it, is to state its quantity, to say how many of it there are. For example, in Tutorial D the expression CO

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table