From oracle-l-bounce@freelists.org Sat Apr 9 05:13:58 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j39ADviM028606 for ; Sat, 9 Apr 2005 05:13:57 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j39ADvem028602 for ; Sat, 9 Apr 2005 05:13:57 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 41BE993C3B; Sat, 9 Apr 2005 04:11:51 -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 29739-01; Sat, 9 Apr 2005 04:11:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B0E1A92F40; Sat, 9 Apr 2005 04:11:50 -0500 (EST) From: "Lex de Haan" To: , "'rjamya'" Cc: , Subject: RE: Index on status field? Date: Sat, 9 Apr 2005 11:09:58 +0200 MIME-Version: 1.0 Content-type: text/plain In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcU8m15SftnCtjybQAqQGAbuC6KdZwAR1z3g Message-Id: <20050409090958.A9D8EB000577@smtp-out2.tiscali.nl> Content-Transfer-Encoding: 8bit X-archive-position: 18248 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lex.de.haan@naturaljoin.nl Precedence: normal Reply-To: lex.de.haan@naturaljoin.nl X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: ... and of course, while using this trick, we all realize that we are not supposed to use nulls to represent a value ;-) so if you like "correct" SQL results under all circumstances, you'll probably need some SQL functions here and there in your code. indeed, *not* in the create index statement, and *not* in the constraint definition, but those two statements you typically perform only once... ah, that felt good ... cheers, Lex. --------------------------------------------- Visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- ... all you need to do is go from Y/N to Y/NULL and you don't need to use the FBI nor do you need the awkward WHERE clause like (DECODE(temporary, 'Y', 'Y', NULL)) ... I prefer Y/NULL with a check constraint that makes sure those are the only valid values. S- -- http://www.freelists.org/webpage/oracle-l