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

Yes, but did not get very far in my attempts.  My ideas were not
syntactically correct from the error messages I was getting, so I did
not know if it was possible.  I also tried searching google and asktom
to see if I could get any light shed on the subject.



Thanks for your concern,



Rick Stephenson



__ ____ ____ ____ ____ ____ ____

From: Jared Still [mailto:jkstill@(protected)]
Sent: Tuesday, December 06, 2005 11:52 AM
To: Rick Stephenson
Cc: oracle-l@(protected)
Subject: Re: Function based indexes?



Have you tried it?



On 12/6/05, Rick Stephenson <RStephenson@(protected)> wrote:

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






--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


<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:blue;
  text-decoration:underline;}
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-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=blue>

<div class=Section1>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Yes, but did not get very far in my
attempts.&nbsp; My ideas were not syntactically correct from the error messages
I
was getting, so I did not know if it was possible.&nbsp; I also tried searching
google and asktom to see if I could get any light shed on the subject.<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 for your concern,<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'> Jared Still
[mailto:jkstill@(protected)] <br>
<b><span style='font-weight:bold'>Sent:</span></b> Tuesday, December 06, 2005
11:52 AM<br>
<b><span style='font-weight:bold'>To:</span></b> Rick Stephenson<br>
<b><span style='font-weight:bold'>Cc:</span></b> 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 style='margin-bottom:12.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>Have you tried it?<br>
<br>
<o:p></o:p></span></font></p>

<div>

<p class=MsoNormal><span class=gmailquote><font size=3 face="Times New Roman">
<span
style='font-size:12.0pt'>On 12/6/05, <b><span style='font-weight:bold'>Rick
Stephenson</span></b> &lt;<a href="mailto:RStephenson@(protected)">RStephenson
@(protected)</a>&gt;
wrote:</span></font></span><o:p></o:p></p>

<div>

<p><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't query
on any other value, so I don't want to consume the space for the other
values.&nbsp; I am running EE <a href="http://9.2.0.3" target="_blank">9.2.0.3<
/a>.</span></font><o:p></o:p></p>

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

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

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

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

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

</div>

</div>

<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><br>
<br clear=all>
<br>
-- <br>
Jared Still<br>
Certifiable Oracle DBA and Part Time Perl Evangelist<o:p></o:p></span></font><
/p>

</div>

</body>

</html>