NULL values

From Robs_Wiki
Jump to: navigation, search

Introduction

A NULL value in databases is a systemvalue that takes up one byte ofstorage and indicates that a value isnot present as opposed to a space orzero or any other default value. Thefield in a database containing theNULL value means that the content ofthis cell is unknown at the time oflooking at it. A column that allowsNULL values also allows rows to beinserted with no values at all in thatcolumn. There are several pros andcons of using NULL values as opposedto default values:

Pros

  • NULL value does not have the datatype, therefore can be inserted to anydata structure and any databasecolumn. Default values, on the otherhand, need to have their data typespecified and a default value in onecolumn might look the same in anothercolumn, but it might be of a differenttype.
  • NULL is often used in schemas where avalue is optional. It is a convenientmethod for omitting data entry forunknown fields without having toimplement additional rules, likestoring negative values in an integerfield to represent omitted data.
  • Since the NULL value takes up only 1bit of memory space, they may beuseful when optimising the database.Using those values is much moreefficient than default values, e.g.character’s 8 bits and integer’s16bits.
  • While your system requirements maychange over time and the default valuetypes with them, NULL value is alwaysNULL so there is no need to update thetype of data.
  • Assigning Not Null to table schemascan also help with table validation,in a sense that the column with NotNull criteria will require a value tobe inserted. Default values do nothave these capabilities.

Cons

  • NULL values are easily confused withempty character strings, which returna blank value to the user whenselected. In this sense, defaultvalues are less confusing and are thesafer option, unless the default valueis set to the empty string.
  • If NULL values are allowed in thedatabase, they may cause the designersome extra time and work as they canmake the database logic morecomplicated, especially when there area lot of comparisons to null values inplace.

Null values and indexes

Something about nothing. A frequently misunderstood concept with NULLS and indexes is the myth that NULL values are not tracked in “normal” indexes such as a conventional B*Tree index. Therefore, many people conclude erroneously that “WHERE COLUMN IS NULL” will not use, in fact cannot, use an index.The facts are:

  • Entirely NULL keys are not entered into a ‘normal’ B*Tree in Oracle
  • Therefore, if you have a concatenated index on say C1 and C2, then you will likely find NULL values in it – since you could have a row where C1 is NULL but C2 is NOT NULL – that key value will be in the index.
  • In fact – even if the table did not have any NOT NULL columns, or we didn’t want/need to have a concatenated index involving OWNER – there is a transparent way to find the NULL OBJECT_ID values rather easily:
SQL> create index t_idx_new on t(object_id,0);