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 Rick Stephenson

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

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




<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft
-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http:
//www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[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>
<!--
/* Font Definitions */
@(protected)
  {font-family:Tahoma;
  panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
  {margin:0in;
  margin-bottom:.0001pt;
  font-size:12.0pt;
  font-family:"Times New Roman";}
a:link, span.MsoHyperlink
  {color:blue;
  text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
  {color:purple;
  text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
  {margin:0in;
  margin-bottom:.0001pt;
  font-size:10.0pt;
  font-family:"Courier New";}
p.MsoAutoSig, li.MsoAutoSig, div.MsoAutoSig
  {margin:0in;
  margin-bottom:.0001pt;
  font-size:12.0pt;
  font-family:"Times New Roman";}
p
  {mso-margin-top-alt:auto;
  margin-right:0in;
  mso-margin-bottom-alt:auto;
  margin-left:0in;
  font-size:12.0pt;
  font-family:"Times New Roman";}
span.EmailStyle19
  {mso-style-type:personal;
  font-family:Arial;
  color:windowtext;}
span.EmailStyle21
  {mso-style-type:personal-reply;
  font-family:Arial;
  color:navy;}
@(protected) Section1
  {size:8.5in 11.0in;
  margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
  {page:Section1;}
-->
</style>

</head>

<body lang=EN-US link=blue vlink=purple>

<div class=Section1>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>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 size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Thanks,<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<div>

<p class=MsoAutoSig><font size=3 color=navy face="Times New Roman"><span
style='font-size:12.0pt;color:navy'>Rick Stephenson<o:p></o:p></span></font></p>

</div>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<div>

<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>

<hr size=2 width="100%" align=center tabindex=-1>

</span></font></div>

<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;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 size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New";color:blue'>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 size=3 face="Times New Roman"><span style='font-size:
12.0pt'>&nbsp;<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New";color:blue'>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 size=3 face="Times New Roman"><span style='font-size:
12.0pt'>&nbsp;<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New";color:blue'>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 size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

<!-- Converted from text/plain format -->

<p><font size=2 face="Courier New"><span style='font-size:10.0pt;font-family:
"Courier New"'>kind regards,<br>
<br>
Lex.<br>
<br>
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------<br>
Jonathan Lewis Seminar </span></font><font size=2><span style='font-size:10.0pt
'><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 size=2 face="Courier New"><span style='font-size:10.0pt;
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 size=3 face="Times New Roman"><span style='font-size:
12.0pt'>&nbsp;<o:p></o:p></span></font></p>

</div>

<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>

<hr size=2 width="100%" align=center tabIndex=-1>

</span></font></div>

<p class=MsoNormal style='margin-bottom:12.0pt'><b><font size=2 face=Tahoma>
<span
style='font-size:10.0pt;font-family:Tahoma;font-weight:bold'>From:</span></font
></b><font
size=2 face=Tahoma><span style='font-size:10.0pt;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 size=2 face=Arial><span style='font-size:10.0pt;
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 size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks,<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoAutoSig><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Rick Stephenson<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

</div>

</body>

</html>