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

Translate the given er schema into sql, Consider the schema for FreeCheckin... Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign

Using extend - collection method, Using EXTEND To enlarge the size of ... Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Assignment statement in pl sql, Assignment Statement: The assignment s... Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Update statement - syntax, UPDATE Statement   The UPDATE statement tra... UPDATE Statement   The UPDATE statement transforms the values of the specified columns in one or more rows in the table or view. Syntax:

Procedure, 1. Create a procedure called TAX_COST_SP to accomplish the tax c... 1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

Inserting objects in pl sql, Inserting Objects: You can use the INSERT... Inserting Objects: You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

Obtaining a natural join by specifying the common columns, Obtaining a natu... Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe

Autonomous versus nested transactions, Autonomous versus Nested Transaction... Autonomous versus Nested Transactions Though an autonomous transaction is started by the other transaction, it is not a nested transaction for the reasons shown below: (i)

Cosmozoic theory - origin of life, COSMOZOI C THEORY - Richter (1865... COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

Sql is a database language, SQL Is a Database Language: The commands g... SQL Is a Database Language: The commands given to a DBMS by an application are written in the database language of the DBMS. The term data sublanguage is sometimes used instea