RE: Function based indexes?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 6 Dec 2005 19:58:46 +0100
Message-ID: <001c01c5fa97$16660e80$6401a8c0@LAPTOPLEX>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rick Stephenson
Sent: Tuesday, December 06, 2005 19:45
To: oracle-l_at_freelists.org
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  

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 06 2005 - 13:00:59 CST