Re: O9i: general index question

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 3 Apr 2007 05:59:34 -0700
Message-ID: <1175605174.577261.286070@b75g2000hsg.googlegroups.com>

On 3 Apr., 14:50, "Thorsten Kettner" <thorsten.kett..._at_web.de> wrote:
> CREATE INDEX idxBaeumeNebenanl ON tbBaeume
> (decode(CIDNEBENANLAGE,'0000000000',0,1));
>
> SELECT ... WHERE decode(CIDNEBENANLAGE,'0000000000',0,1) = 1

Hm, having mentioned that NULL is not stored in indexes you can extremely reduce the index size thus:

CREATE INDEX idxBaeumeNebenanl ON
tbBaeume(decode(CIDNEBENANLAGE,'0000000000',NULL,1));

SELECT ... WHERE decode(CIDNEBENANLAGE,'0000000000',NULL,1) = 1

(You see, when establishing computed indexes you should know about them and use the exact same expression in your select). Received on Tue Apr 03 2007 - 07:59:34 CDT