| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Function based indexes
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. 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.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=510273714-23082000></SPAN></FONT> </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? 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. </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>> Hi!</FONT> <BR><FONT size=2>> </FONT><BR><FONT
size=2>> This is 8.1.6 on AIX.</FONT> <BR><FONT size=2>>
</FONT><BR><FONT size=2>> Is there any way that I can put a function based
index on </FONT><BR><FONT size=2>> this expression???</FONT> <BR><FONT
size=2>> </FONT><BR><FONT size=2>> ....</FONT> <BR><FONT size=2>>
where table.Closedstamp between</FONT> <BR><FONT size=2>>
GETSTOREDATESTR(table.Companyid,table.StoreLocid,'08/01/2000') AND</FONT>
<BR><FONT size=2>> </FONT><BR><FONT size=2>>
GETSTOREDATE(table.Companyid,table.StoreLocid,TRUNC(TO_DATE</FONT> <BR><FONT
size=2>> ('08/01/2000','MM/DD/YYYY')+1))</FONT> <BR><FONT size=2>>
</FONT><BR><FONT size=2>> Or would it help to put a function based index on
the two functions</FONT> <BR><FONT size=2>> GETSTOREDATESTR and
GETSTOREDATE.</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>>
</FONT><BR><FONT size=2>> Thanks,</FONT> <BR><FONT size=2>>
Helmut</FONT> </P>
<P><FONT size=2>-- </FONT><BR><FONT size=2>Author: Helmut Daiminger</FONT>
<BR><FONT size=2> INET: hdaiminger_at_vivonet.com</FONT> </P>
<P><FONT size=2>Fat City Network Services -- (858)
538-5051 FAX: (858) 538-5051</FONT> <BR><FONT size=2>San Diego,
California -- 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). 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
 |
 |