Re: how expensive are null values?

If a column is going to appear in the where clause of a query, you should avoid NULLs, unless they have some semantic meaning you wish to preserve by allowing NULLS. If a column can have a valid default such as 'UNKNOWN', that is preferable to a NULL. Querying for "column is null", will usually blow your indexes.

You can trick Oracle into using indexes when looking for NULLs by using a function based index:

CREATE INDEX <SOME_INDEX> ON <SOME_TABLE>(NVL(<SOME_COLUMN>,'NULL');

The only trap is that you must pick the sentinel value for the NVL function carefully so that it does not actually match a value in the column.

Oracle will ignore the function based index unless you analyze the table. You may have to add a query hint as well, but your performance on large tables with small results sets will be excellent. Depending on your query structure, you might want to make the function based index composite.

Good Luck!

--

~~~~~~~~~~~~~~~~
Chris Weiss
www.hpdbe.com
High Performance Database Engineering
~~~~~~~~~~~~~~~~




"Steffen Ramlow" <s.ramlow_at_gmx.de> wrote in message
news:a2hake$112tge$1_at_ID-54600.news.dfncis.de...


> sample1:

>

> id  col1    col2

> ---------------

> 1   v11     v12

>

>

> versus:

>

> sample2:

>

> id  col1    col2

> ---------------

> 1  v11     null

> 1  null      v12

>

>

> there maybe indexes on the tabel, at least in column id

>

> where r the problems in sample2?

>

>