RE: Function based indexes?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <RStephenson@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Dec 2005 19:58:46 +0100
yes, you can -- more or less -- by mapping all other possible outcomes to NULL
with a CASE expression. Note however that function-based indexes also store
entries for NULL expression outcomes.
By the way, if that value 1 is so special, you might consider to store it in a
separate column. Unless tomorrow the special value is suddenly 42 :-)
but why would you bother about a little bit of space? as long as you don't
perform full index scans, the performance will not be affected by the index
size...
kind regards,
Lex.
---------------------------------------------------------------------
Jonathan Lewis Seminar <http://www.naturaljoin.nl/events/seminars.html>
http://www.naturaljoin.nl/events/seminars.html
---------------------------------------------------------------------
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Rick Stephenson
Sent: Tuesday, December 06, 2005 19:45
To: oracle-l@xxxxxxxxxxxxx
Subject: Function based indexes?
Is there a way to create an index on a column and only index those where the
value meets a certain expression? For example, if I have a numeric column, can
I just have the index built for those values that equal 1? I don't query on any
other value, so I don't want to consume the space for the other values. I am
running EE 9.2.0.3.
Thanks,
Rick Stephenson
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
