| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: No Nulls? (was: Warehouse design: snowflake vs star schem
Yes - the right answer. And can be validated with a little basic normalisation.
peter
edinburgh
>
>
> Thinking about Matt's question, would it be "proper" to move
> the column to a
> EMP_TERMINATED table with an outer join on EMPNO? There
> wouldn’t be any
> NULLs...
>
> Rich
>
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International,
> Sussex, WI USA
>
> -----Original Message-----
> Sent: Monday, October 14, 2002 4:53 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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-----
> Sent: Monday, October 14, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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-----
> Sent: Monday, October 14, 2002 2:14 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
>
>
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L
> Sent: Monday, October 14, 2002 3:39 PM
>
>
> "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-----
> Sent: Monday, October 14, 2002 2:29 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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, Susse
> --
> 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).
>
scan all attachments. http://www.bgs.ac.uk *********************************************************************
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter INET: pgro_at_bgs.ac.uk 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).Received on Tue Oct 15 2002 - 06:38:20 CDT
 |
 |