From oracle-l-bounce@freelists.org Fri Sep 24 08:39:32 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8ODdWh12612 for ; Fri, 24 Sep 2004 08:39:32 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8ODdWI12606 for ; Fri, 24 Sep 2004 08:39:32 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6B35672EA12; Fri, 24 Sep 2004 08:45:33 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32498-29; Fri, 24 Sep 2004 08:45:33 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6A2D372EA11; Fri, 24 Sep 2004 08:45:32 -0500 (EST) Message-ID: <6BA0194B4809D9118361000F1F6C95105BE726@exchmn4.lifetouch.com> From: STEVE OLLIG To: oracle-l@freelists.org Subject: RE: artificial values vs null Date: Fri, 24 Sep 2004 08:45:29 -0500 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 10070 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sollig@lifetouch.com Precedence: normal Reply-To: sollig@lifetouch.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I'm not a NULLophobe (one who fears NULLs), but have an example where it might make sense to use an artificial value instead of a NULL. I'd be interested in hearing other's thoughts on this specific case. This is a star schema datamart and the should be NULL in question is the PK of the time dimension. I'm relatively new to datawarehousing, and we're building a new datamart. So I cannot contend this is will stand the test of time. But some members of my team are seasoned datawarehousers, so I don't think we're too far out in left field with this approach. We happened to choose January 1, 1900 as the artificial "Unknown" date in timedim (our company didn't exist in 1900 so our mart would care little about anything that may have happened then). All facts who have an unknown date have this value. Some dates are never unknown (transaction_date), but others can be (product_ship_date). Seems to be working well for us. FWIW, Oracle Discoverer is the user facing app for this mart. I think we could have set Discoverer up to work with NULL dates, but inner joins are the default for relating a fact to a dimension. I inherently shy away from having to tweak the default behavior of such an app so significantly. So what would a NULL zealot think of such blasphemy? > > On Fri, 24 Sep 2004 21:51:33 +1000, Nuno Souto > wrote: > > ----- Original Message ----- > > From: "Niall Litchfield" > > >> Whatever the optimiser may do with 31 dec 2099, > > >> it will be a darn long shot better than a NULL value > > >> that can't be indexed... > > > > > > you may regret making that statement.... see below. > > -- http://www.freelists.org/webpage/oracle-l