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...
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Rick Stephenson Sent: Tuesday, December 06, 2005 19:45 To: oracle-l@(protected) 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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o = "urn:schemas-microsoft-com:office:office" xmlns:w = "urn:schemas-microsoft-com:office:word"><HEAD> <META http-equiv=Content-Type content="text/html; charset=us-ascii"> <META content="MSHTML 6.00.2900.2769" name=GENERATOR> <STYLE>@(protected) Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; } P.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } LI.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } DIV.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } A:link { COLOR: blue; TEXT-DECORATION: underline } SPAN.MsoHyperlink { COLOR: blue; TEXT-DECORATION: underline } A:visited { COLOR: purple; TEXT-DECORATION: underline } SPAN.MsoHyperlinkFollowed { COLOR: purple; TEXT-DECORATION: underline } P.MsoPlainText { FONT-SIZE: 10pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Courier New" } LI.MsoPlainText { FONT-SIZE: 10pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Courier New" } DIV.MsoPlainText { FONT-SIZE: 10pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Courier New" } P.MsoAutoSig { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } LI.MsoAutoSig { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } DIV.MsoAutoSig { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } SPAN.EmailStyle17 { COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal-compose } DIV.Section1 { page: Section1 } </STYLE> </HEAD> <BODY lang=EN-US vLink=purple link=blue> <DIV dir=ltr align=left><SPAN class=073215318-06122005><FONT face="Courier New" color=#0000ff size=2>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.</FONT></SPAN></DIV> <DIV dir=ltr align=left><SPAN class=073215318-06122005><FONT face="Courier New" color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=073215318-06122005><FONT face="Courier New" color=#0000ff size=2>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 :-)</FONT></SPAN></DIV> <DIV dir=ltr align=left><SPAN class=073215318-06122005><FONT face="Courier New" color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=073215318-06122005><FONT face="Courier New" color=#0000ff size=2>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...</FONT></SPAN></DIV><!-- Converted from text/plain format - -><FONT color=#0000ff size=2></FONT><FONT color=#0000ff size=2></FONT><BR> <P><FONT size=2><FONT face="Courier New">kind regards,<BR><BR>Lex.<BR><BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- --- -- ---- ---- -----<BR>Jonathan Lewis Seminar </FONT><A href="http://www.naturaljoin.nl/events/seminars.html"><FONT face="Courier New">http://www.naturaljoin.nl/events/seminars.html</FONT></A><BR ><FONT face="Courier New">-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- -- ------</FONT></FONT><FONT face="Courier New"> </FONT></P> <DIV> </DIV><BR> <DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left> <HR tabIndex=-1> <FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Rick Stephenson<BR><B>Sent:</B> Tuesday, December 06, 2005 19:45<BR><B>To:</B> oracle-l@(protected)<BR><B>Subject:</B> Function based indexes?<BR></FONT><BR></DIV> <DIV></DIV> <DIV class=Section1> <P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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.<o:p></o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks,<o:p></o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P> <P class=MsoAutoSig><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">Rick Stephenson<o:p></o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P></DIV></BODY></HTML>
