Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: NULLS vs. DUMMYING DATA


From: Cary Millsap <>
Date: Thu, 08 May 2003 12:32:35 -0800
Message-ID: <>

Just make sure you carefully choose which "value" to use nulls for. Dave Ensor's O'Reilly book shows a couple of good examples in which using nulls the right way can save a lot of space and time.  

Cary Millsap
Hotsos Enterprises, Ltd.

Upcoming events:
- Hotsos Clinic <> 101 in London,
Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit for schedule details...

-----Original Message-----

Sent: Thursday, May 08, 2003 2:32 PM
To: Multiple recipients of list ORACLE-L  

I happen to think that "dummying up data" is a BAD idea no matter where it is. True, your program has to handle the nulls, but that sure is better than indexing a pile of spaces or 0's.  

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----

Sent: Thursday, May 08, 2003 3:00 PM
To: Multiple recipients of list ORACLE-L

"dummying data"


I am not sure what is the best way to deal with this and would like a concensus. If this subject has been talked about before I apologize. The issue is nulls or "dummying" data in an OLTP and also in a Data Warehouse.

-I think that an OLTP system should be considered differently than a data
warehouse as it is the source system.
-For the most efficient data warehouses using star schemas, bitmapped
indexes, and dimensions I can't see using nulls working as well (even with functional indexes) as you can't control how people will access the data and nulls would invalidate the use of an index - on a large DW this could be very very bad.

-I have a system where the components of a date can be as important as the
entire date itself. Therefore, where the components of the date are correct I would keep them and load as part of the date in a date datatype.  

Example: February 31, 1999

The concern I have is the month, day, year can all be important depending on the analysis done. So storing these as separate numeric (to cut-down on the junk allowed as input) separately makes sense but would also like to store as date whenever there is date conversion possible. However, what if month and year are correct but day is wrong:

February 31, 1999

-store as missing date in date field (date that actually out of range didn't
exist in history)
-then store as separate numeric fields

-store as date when I can (no missing date)
-store as separate numeric fields



Please see the official ORACLE-L FAQ:

Author: Cary Millsap

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 08 2003 - 15:32:35 CDT

Original text of this message