Rich,
NULLs are one of those religious war topics.
They have a legitimate place in an RDBMS, but I think you
need to be judicious in using them.
If a column is to be used as part of a where clause, I prefer
to use a default value, particularly with dates.
NULLs can be a real performance killer when used this way.
Here's an example where a begin date is known, the end
date may be specifed or not. If not, it is NULL.
NULL:
select *
from mytab
where begin_date >= to_date('06/18/2001','mm/dd/yyyy')
and (
end_date <= ' to_date('08/30/2001','mm/dd/yyyy')
or
end_date is NULL
)
The problem with that one is obvious. The SQL is a little convoluted
and it's going to be slow due to the NULL.
DEFAULT VALUE of 12/31/4712 for unknown or unspecified end_date:
select *
from mytab
where begin_date >= to_date('06/18/2001','mm/dd/yyyy')
and (
end_date <= ' to_date('08/30/2001','mm/dd/yyyy')
or
end_date = to_date('12/31/4712','mm/dd/yyyy')
)
Jared
"Jesse, Rich" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/14/2002 10:33 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: No Nulls? (was: Warehouse design: snowflake vs star schemas)
On the link below is this quote from C.J.Date:
"I don't want you to think that my SQL solution to your problem means I
advocate the use of nulls. Nulls are a disaster."
Of course, he doesn't expound upon it (probably not a need except for
dummies like me). Anyone care to comment? (On the quote, not on my
dumminess...)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI
USA
> -----Original Message-----
> From: Robson, Peter [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 14, 2002 4:59 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Warehouse design: snowflake vs star schemas
>
>
> Just for the record (and perhaps to confirm that there are
> always two sides
> to a story). Readers may like to see the article Chris Date
> wrote to Ralph
> Kemball on the subject of business rules and integrity constraints:
>
> http://www.dbdebunk.com/kimball1.htm
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
