Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
Function based indexes?

Function based indexes?

2005-12-06       - By Bobak, Mark

Reply:     1     2     3     4     5     6     7     8     9     10     >>  

Not at all.  For multiple values, you just need a slightly more complex
CASE statement:
(Borrowing from Jonathan's example)
create index i2 on t2(case n1 when 1 then 1 when 2 then 2 when 3 then 3
end);

Of course, this will work for a small handful of values, but, you don't
want to go overboard....



Hope that helps,



-Mark


__ ____ ____ ____ ____ ____ ____

From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Rick Stephenson
Sent: Tuesday, December 06, 2005 5:31 PM
To: oracle-l@(protected)
Subject: RE: Function based indexes?



I had not thought about doing it that way.  The table has about 300
million rows, 10,000 of which would contain the value I am searching for
which is why I only wanted to see if there was a way to create an index
for just 1 value in a column.  It will be stored in a separate column.
So, as long as I store the value I want and leave the rest NULL, I can
accomplish what I am after.  Does that mean there is no other way to
accomplish this task if I have multiple values?



Thanks,



Rick Stephenson



__ ____ ____ ____ ____ ____ ____

From: Lex de Haan [mailto:lex.de.haan@(protected)]
Sent: Tuesday, December 06, 2005 11:59 AM
To: Rick Stephenson; oracle-l@(protected)
Subject: RE: Function based indexes?



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@(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:v =
"urn:schemas-microsoft-com:vml" 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><!--[if !mso]>
<STYLE>v\:* {
  BEHAVIOR: url(#default#VML)
}
o\:* {
  BEHAVIOR: url(#default#VML)
}
w\:* {
  BEHAVIOR: url(#default#VML)
}
.shape {
  BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]-->
<STYLE>@(protected) {
  font-family: Tahoma;
}
@(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"
}
P {
  FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New
Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
SPAN.EmailStyle19 {
  COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal
}
SPAN.EmailStyle21 {
  COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply
}
DIV.Section1 {
  page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><SPAN class=366253622-06122005><FONT face=Arial
color=#0000ff size=2>Not at all.&nbsp; For multiple values, you just need a
slightly more complex CASE statement:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=366253622-06122005><FONT face=Arial
color=#0000ff size=2>(Borrowing from Jonathan's example)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=366253622-06122005><FONT face=Arial
size=2><FONT size=2>
<P><FONT color=#0000ff>create index i2 on t2(case n1 when 1 then 1&nbsp;<SPAN
class=366253622-06122005>when 2 then 2 when 3 then 3 </SPAN>end);</FONT></P>
<P><SPAN class=366253622-06122005><FONT color=#0000ff>Of course, this will work
for a small handful of values, but, you don't want to go
overboard....</FONT></SPAN></P>
<P><SPAN class=366253622-06122005><FONT color=#0000ff></FONT></SPAN>&nbsp;</P>
<P><SPAN class=366253622-06122005><FONT color=#0000ff>Hope that
helps,</FONT></SPAN></P>
<P><SPAN class=366253622-06122005><FONT color=#0000ff></FONT></SPAN>&nbsp;</P>
<P><SPAN class=366253622-06122005><FONT
color=#0000ff>-Mark</FONT></SPAN></P></FONT></FONT></SPAN></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 5:31 PM<BR><B>To:</B>
oracle-l@(protected)<BR><B>Subject:</B> RE: Function based
indexes?<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">I had not thought
about
doing it that way.&nbsp; The table has about 300 million rows, 10,000 of which
would contain the value I am searching for which is why I only wanted to see if
there was a way to create an index for just 1 value in a column.&nbsp; It will
be stored in a separate column.&nbsp; So, as long as I store the value I want
and leave the rest NULL, I can accomplish what I am after.&nbsp; Does that mean
there is no other way to accomplish this task if I have multiple
values?<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p>&nbsp;</o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Thanks,<o:p></o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p>&nbsp;</o:p><
/SPAN></FONT></P>
<DIV>
<P class=MsoAutoSig><FONT face="Times New Roman" color=navy size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: navy">Rick
Stephenson<o:p></o:p></SPAN></FONT></P></DIV>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p>&nbsp;</o:p><
/SPAN></FONT></P>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN><
/FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> Lex de
Haan [mailto:lex.de.haan@(protected)] <BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Tuesday, December 06, 2005 11:59
AM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> Rick Stephenson;
oracle-l@(protected)<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B
>
RE: Function based indexes?</SPAN></FONT><o:p></o:p></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p>&nbsp;</o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'">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.</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'">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
:-)</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'">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...</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p>&nbsp;</o:p></SPAN></FONT></P><!-- Converted from
text/plain format -->
<P><FONT face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">kind
regards,<BR><BR>Lex.<BR><BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
-- ---- ---- -----<BR>Jonathan
Lewis Seminar </SPAN></FONT><FONT size=2><SPAN style="FONT-SIZE: 10pt"><A
href="http://www.naturaljoin.nl/events/seminars.html"><FONT
face="Courier New"><SPAN
style="FONT-FAMILY: 'Courier New'">http://www.naturaljoin.nl/events/seminars
.html</SPAN></FONT></A><BR></SPAN></FONT><FONT
face="Courier New" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">-- ---- ---- ---- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- ----</SPAN></FONT><FONT
face="Courier New"><SPAN style="FONT-FAMILY: 'Courier New'">
</SPAN></FONT><o:p></o:p></P>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">&nbsp;<o:p></o:p></SPAN></FONT></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p>&nbsp;</o:p></SPAN></FONT></P>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt"><B><FONT face=Tahoma size=2>
<SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN><
/FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B><SPAN
style="FONT-WEIGHT: bold">On Behalf Of </SPAN></B>Rick Stephenson<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Tuesday, December 06, 2005
19:45<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
oracle-l@(protected)<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B
>
Function based indexes?</SPAN></FONT><o:p></o:p></P>
<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?&nbsp;
For example, if I have a numeric column, can I just have the index built for
those values that equal 1?&nbsp; I don&#8217;t query on any other value, so I
don&#8217;t
want to consume the space for the other values.&nbsp; 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>&nbsp;</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>&nbsp;</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>&nbsp;</o:p></SPAN></FONT></P></DIV></BODY></HTML>