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 > [EMAIL PROTECTED] 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 > [EMAIL PROTECTED] > > > > ----- 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 - [EMAIL PROTECTED] > 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" <[EMAIL PROTECTED]> > 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 > [EMAIL PROTECTED] Quad/Tech International, Susse > -- > 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). > ********************************************************************* This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. The BGS cannot accept any responsibility for viruses, so please scan all attachments. http://www.bgs.ac.uk ********************************************************************* -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robson, Peter 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).
