Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Function based indexes

RE: Function based indexes

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Wed, 23 Aug 2000 10:36:30 -0400
Message-Id: <10598.115374@fatcity.com>


This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C00D0F.86B96E46
Content-Type: text/plain;

        charset="iso-8859-1"

Yes, I've successfully created function-based indices -- I use them to put unique constraints on case-insensitive names. The create just fine and do a good job at their constraint function, but I haven't seen them come up in explain plans in any logical fashion. So...the jury is still out on their usefulness, in my opinion.  

Diana

-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu_at_qode.com] Sent: Wednesday, August 23, 2000 8:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Function based indexes

I'm sorry Helmut I do not have an answer for you.

I wanted to ask the list, has anyone successfully created a function-based index before? I tried to create one on a medium-sized table and it ran for hours and hours. (8.1.5, hp/ux), it was only UPPER(field_name). After watching that run I did not pursue it any further.

Man, Intermedia, fxn-idx's, OAS, all these mediocre-working things in Oracle are leaving a bad taste in my mouth.

-----Original Message-----
From: Helmut Daiminger [ mailto:hdaiminger_at_vivonet.com <mailto:hdaiminger_at_vivonet.com> ]
Sent: Tuesday, August 22, 2000 5:12 PM
To: Multiple recipients of list ORACLE-L Subject: Function based indexes

> Hi!
>
> This is 8.1.6 on AIX.
>
> Is there any way that I can put a function based index on
> this expression???
>
> ....
> where table.Closedstamp between
> GETSTOREDATESTR(table.Companyid,table.StoreLocid,'08/01/2000') AND
>
> GETSTOREDATE(table.Companyid,table.StoreLocid,TRUNC(TO_DATE
> ('08/01/2000','MM/DD/YYYY')+1))
>
> Or would it help to put a function based index on the two functions
> GETSTOREDATESTR and GETSTOREDATE.
>
>
> Thanks,
> Helmut

-- 
Author: Helmut Daiminger 
  INET: hdaiminger_at_vivonet.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 


------_=_NextPart_001_01C00D0F.86B96E46
Content-Type: text/html;
	charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: Function based indexes</TITLE>

<META content="MSHTML 5.00.3017.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=510273714-23082000>Yes, 
I've successfully created function-based indices -- I use them to put unique 
constraints on case-insensitive names.&nbsp; The create just fine and do a good 
job at their constraint function, but I haven't seen them come up in explain 
plans in any logical fashion.&nbsp; So...the jury is still out on their 
usefulness, in my opinion.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN 
class=510273714-23082000></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN 
class=510273714-23082000>Diana</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
  <DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma 
  size=2>-----Original Message-----<BR><B>From:</B> Koivu, Lisa 
  [mailto:lkoivu_at_qode.com]<BR><B>Sent:</B> Wednesday, August 23, 2000 8:59 
  AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: 
  Function based indexes<BR><BR></DIV></FONT>
  <P><FONT size=2>I'm sorry Helmut I do not have an answer for you. </FONT></P>
  <P><FONT size=2>I wanted to ask the list, has anyone successfully created a 
  function-based index before?&nbsp; I tried to create one on a medium-sized 
  table and it ran for hours and hours.&nbsp; (8.1.5, hp/ux), it was only 
  UPPER(field_name).&nbsp; After watching that run I did not pursue it any 
  further.&nbsp; </FONT></P>
  <P><FONT size=2>Man, Intermedia, fxn-idx's, OAS, all these mediocre-working 
  things in Oracle are leaving a bad taste in my mouth. </FONT></P>
  <P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: 
  Helmut Daiminger [<A 
  href="mailto:hdaiminger_at_vivonet.com">mailto:hdaiminger_at_vivonet.com</A>]</FONT> 
  <BR><FONT size=2>Sent: Tuesday, August 22, 2000 5:12 PM</FONT> <BR><FONT 
  size=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT 
  size=2>Subject: Function based indexes</FONT> </P><BR>
  <P><FONT size=2>&gt; Hi!</FONT> <BR><FONT size=2>&gt; </FONT><BR><FONT 
  size=2>&gt; This is 8.1.6 on AIX.</FONT> <BR><FONT size=2>&gt; 
  </FONT><BR><FONT size=2>&gt; Is there any way that I can put a function based 
  index on </FONT><BR><FONT size=2>&gt; this expression???</FONT> <BR><FONT 
  size=2>&gt; </FONT><BR><FONT size=2>&gt; ....</FONT> <BR><FONT size=2>&gt; 
  where table.Closedstamp between</FONT> <BR><FONT size=2>&gt; 
  GETSTOREDATESTR(table.Companyid,table.StoreLocid,'08/01/2000') AND</FONT> 
  <BR><FONT size=2>&gt; </FONT><BR><FONT size=2>&gt; 
  GETSTOREDATE(table.Companyid,table.StoreLocid,TRUNC(TO_DATE</FONT> <BR><FONT 
  size=2>&gt; ('08/01/2000','MM/DD/YYYY')+1))</FONT> <BR><FONT size=2>&gt; 
  </FONT><BR><FONT size=2>&gt; Or would it help to put a function based index on 
  the two functions</FONT> <BR><FONT size=2>&gt; GETSTOREDATESTR and 
  GETSTOREDATE.</FONT> <BR><FONT size=2>&gt; </FONT><BR><FONT size=2>&gt; 
  </FONT><BR><FONT size=2>&gt; Thanks,</FONT> <BR><FONT size=2>&gt; 
  Helmut</FONT> </P>
  <P><FONT size=2>-- </FONT><BR><FONT size=2>Author: Helmut Daiminger</FONT> 
  <BR><FONT size=2>&nbsp; INET: hdaiminger_at_vivonet.com</FONT> </P>
  <P><FONT size=2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 
  538-5051&nbsp; FAX: (858) 538-5051</FONT> <BR><FONT size=2>San Diego, 
  California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet access 
  / Mailing Lists</FONT> <BR><FONT 
  size=2>--------------------------------------------------------------------</FONT> 
  <BR><FONT size=2>To REMOVE yourself from this mailing list, send an E-Mail 
  message</FONT> <BR><FONT size=2>to: ListGuru_at_fatcity.com (note EXACT spelling 
  of 'ListGuru') and in</FONT> <BR><FONT size=2>the message BODY, include a line 
  containing: UNSUB ORACLE-L</FONT> <BR><FONT size=2>(or the name of mailing 
  list you want to be removed from).&nbsp; You may</FONT> <BR><FONT size=2>also 
  send the HELP command for other information (like subscribing).</FONT> 
Received on Wed Aug 23 2000 - 09:36:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US