Re: No Nulls? (was: Warehouse design: snowflake vs star schem

Both would likely do FTS since at any given time more than 50% of your employees will be current (have an end date of 1/1/4000' making it very unlikely that the cbo would choose this index. The RBO, would, but it would likely degrade not improve your performance.

John

Grabowy, Chris wrote:

> Hmmm...but what about the index? Which is faster?
>
> select * from table where END_EMPLOYMENT IS NULL;
>
> OR
>
> select * from table where END_EMPLOYMENT = '01/01/4000';
>
> I like NULL, but I am leaning towards Igor, and others, to agree upon
> and use a default value, or a "business sense" replacement value for
> NULL. I want to be able to take the awesome advantage of
> an index...versus FTS?
>
> Am I headed in the wrong direction?? Any other thoughts??
>
> -----Original Message-----
> From: Fink, Dan [mailto:Dan.Fink_at_mdx.com]
> Sent: Monday, October 14, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: No Nulls? (was: Warehouse design: snowflake vs star schem
>
> The problem I see with NO NULLS is that artificial data must be
> created, where the data is truly not known. Whether you deal with
> NULLs or artificial data, you will always have to code
> accordingly, so it is a wash. Igor's example is an good one. When
> I write an app to access the END_EMPLOYMENT date, I must handle a
> date of '01/01/4000'. Or I can handle the NULL condition. As a
> person who has had to support some very convoluted code, I'd
> rather deal with NULL. What if the employee record contained
> TERM_CODE? I would rather have the value NULL, meaning they have
> not been terminated rather than dealing with hard-coded or lookup
> values.
>
> -----Original Message-----
> From: Igor Neyman [mailto:ineyman_at_perceptron.com]
> Sent: Monday, October 14, 2002 2:14 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: No Nulls? (was: Warehouse design: snowflake vs
> star schem
>
> END_EMPLOYEMENT date for still employed employees equals to
> "01/01/4000" (or any other pre-defined date in distant future).
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com <mailto:ineyman_at_perceptron.com>
>
>
>
>
> ----- Original Message -----
> From: Adams, Matthew (GECP, MABG, 088130)
> <mailto:MATT.ADAMS_at_APPL.GE.COM>
> To: Multiple recipients of list ORACLE-L
> <mailto:ORACLE-L_at_fatcity.com>
> Sent: Monday, October 14, 2002 3:39 PM
> Subject: RE: No Nulls? (was: Warehouse design: snowflake
> vs star schem
>
> "No application that I can reasonably think of should
> use NULLS, except those pre-81
> where there are obsolete columns."
>
> Everytime somebody says this to me, I ask them:
>
> How do you handle still employed employees in an EMPLOYEE
> table
> that contains a END_EMPLOYEMENT date column?
>
> What's your take?
> ----
> Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
> Write a poem about a haircut! But lofty, noble, tragic,
> full of love,
> treachery, retribution, quiet heroism in the face of
> certain doom!
> Six lines, cleverly rhymed, and every word beginning with
> the letter s!
>
> -----Original Message-----
> From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com]
> Sent: Monday, October 14, 2002 2:29 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:No Nulls? (was: Warehouse design: snowflake vs
> star schem
>
>
> Jesse,
>
> I'll refrain from personal comments, but on CJ's
> quote, he's correct. Nulls
> are an oddity. They cannot be true or false
> (<column_name> = NULL or
> <column_name> != NULL), nor can they equal anything. They
> are in effect a third
> logical state of nothingness. You also have to code most
> applications with
> indicator variables to check for their existence. All in
> all a real pain in the
> backside. BUT, if you give me the possibility that nulls
> exist in the data I
> much prefer using them vs. many a third party solution of
> a single space. No
> application that I can reasonably think of should use
> NULLS, except those pre-81
> where there are obsolete columns.
>
> Dick Goulet
>
> ____________________Reply Separator____________________
> Author: "Jesse; Rich" <Rich.Jesse_at_qtiworld.com>
> Date: 10/14/2002 9:33 AM
>
> 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
> Rich.Jesse_at_qtiworld.com Quad/Tech
> International, Sussex, WI USA
>
> > -----Original Message-----
> > From: Robson, Peter [mailto:pgro_at_bgs.ac.uk]
> > 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: Rich.Jesse_at_qtiworld.com
>
> 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: ListGuru_at_fatcity.com (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: dgoulet_at_vicr.com
>
> 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: ListGuru_at_fatcity.com (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: Ora NT DBA
  INET: orantdba_at_netscape.net

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: ListGuru_at_fatcity.com (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).