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

After trying the idea below, it looks as if the null values are not
stored in the index even though it is a function based index.  So, this
might be what I am after.



> create table test (id number);

Table created.



> insert into test values(1);

1 row created.



> insert into test values(1);

1 row created.



> insert into test values(0);

1 row created.



> insert into test values(0);

1 row created.



> create index test_case_idx on test (case when id = 1 then id else null
end);

Index created.



> analyze index test_case_idx validate structure;

Index analyzed.



> select lf_rows from index_stats;



  LF_ROWS

-- ---- --

        2



1 row selected.



Thanks for your help Lex.



Rick Stephenson



__ ____ ____ ____ ____ ____ ____

From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Rick Stephenson
Sent: Tuesday, December 06, 2005 3: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




<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.EmailStyle20
  {mso-style-type:personal;
  font-family:Arial;
  color:windowtext;}
span.EmailStyle21
  {mso-style-type:personal;
  font-family:Arial;
  color:navy;}
span.EmailStyle22
  {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'>After trying the idea below, it looks as
if the null values are not stored in the index even though it is a function
based index.&nbsp; So, this might be what I am after.<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'>&gt; create table test (id number);<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'>Table created.<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'>&gt; insert into test values(1);<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'>1 row created.<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'>&gt; insert into test values(1);<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'>1 row created.<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'>&gt; insert into test values(0);<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'>1 row created.<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'>&gt; insert into test values(0);<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'>1 row created.<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'>&gt; create index test_case_idx on test
(case when id = 1 then id else null end);<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'>Index created.<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'>&gt; analyze index test_case_idx validate
structure;<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'>Index analyzed.<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'>&gt; select lf_rows from index_stats;<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'>&nbsp;&nbsp; LF_ROWS<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></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'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; 2<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'>1 row selected.<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 help Lex.<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'>
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
3:31 PM<br>
<b><span style='font-weight:bold'>To:</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><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>