"Column is Null" Vs "Column=0"

[engineer2100]

Which of these two is better w.r.t performance?

Select x,y,
From a
where C is null

OR

Select x,y,
From a
where C = 0

I can change the data within the table to 0 from null if the later is better. I am looking a rows that run into couple of 1000s

 

[Dagon]

If C is indexed, then you might get some benefit from the "C = 0" query, providing 0 has a low cardinality. However, for a couple of thousand records, it probably isn't worth it. "C is null" will never use an index, but will take up less space in the database.

 

[engineer2100]

Thanks Dagon.Guessed So!

 

[johnherman]

More importantly, do you understand the concept of NULL in a relational database? If yes, then your answer is based on the data and the user requirements.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[engineer2100]

I do understand to some extent. Correct me if I am wrong.

From Oracle's perspective, each null value is stored with some internal representation so when you do a null= null, the expression always evaluations to a null (unknown).

I was trying to disprove someone's claim that Column=null takes more time than column=0.

 

[Dagon]

If you anything = null (or even anything <> null), the expression evaluates to null.

 

[engineer2100]

exactly!!

 

[johnherman]

Conceptually, NULL means the data is missing or not applicable. NULL for DEATH_DATE means the person is either still alive, or we don't know whether they died or not, or we know they died, but don't know the date. Sometimes data architects will "invent" data so that the users can tell whether the person is definitely alive, or whether it's unknown.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Turkbear]

Hi,
The best definition I have seen for NULL ( in RDBMS terms) is
'undeterminable' -
That is, nothing can be determined about the contents, not its type, its value, nothing at all; anything compared to/combined with NULL will necessarily result in a NULL, even NULL compared to itself..

It is almost metaphysical...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jimbopalmer]

An example where NULL kills performance and (made up) false values recover it.

Employee generates a parts list to issue, (often from a work order or just by typing) it contains a issue ticket number which is NULL until issued by the parts counter.

Once the parts counter issues this list, the issue ticket number is added to this field.

From the point of view of the parts person, the most interesting parts lists are those whose issue ticket number is NULL, but that takes a full table scan.

By assigning all unissued parts lists (there are usually about 12 of these) a issue number of -1 instead of NULL, we can now index our (millions of rows) table instead of a full table scan.



I tried to remain child-like, all I acheived was childish.

 

[Dagon]

If you wanted to keep the database "pure", you could get round it by creating a funtion-based index on nvl(ticket_number,-1) and using that in the query.

 

[SantaMufasa]

As a clarification, in the Oracle World, NULL is "an unknown value". Additionally, the "=" operator compares the bits of the expression to the left of the "=" to the bits of the expression to the right of the "=". If there are no bits to compare to, then Oracle says that the result of the comparison is also NULL/unknown.

However, you can successfully ask if the contents of an expression are, themselves, unknown (i.e., NULL). To do this, you compare an expression to NULL with the "is NULL" comparison:

SQL> Select 'TRUE' from dual where null = null;

no rows selected

SQL> c/=/is
  1* Select 'TRUE' from dual where null is null
SQL> /

'TRU
----
TRUE

Notice in the above query, when we used the "=" to compare NULL to NULL, the answer was "not TRUE", but the answer was also "not FALSE"; the answer was "unknown" (i.e., NULL). That is why there were "no rows selected."


If, however, we ask if "an unknown value is an unknown value" (i.e., "...WHERE NULL is NULL") then the result is TRUE.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

Dagon, I dont think NVL and its variants are allowed within FBI's. But its really a good idea. If this was allowed I could ahve solved a major issue troubling us.

 

[Dagon]

It works for me:

create table testind (a number, b number);

Table created.

create index ti1 on testind (nvl(a, -1));

Index created.

 

[engineer2100]

hmm... now TOAD seriously have some issues!!