| 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: SQL functions and 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_01C05940.8BD1ED86
Content-Type: text/plain;
charset="iso-8859-1"
This statement is correct. If you use a function on an indexed field you will not hit the index, unless there is a function-based index on that field.
For example
select count(*)
from table
where UPPER(field1) = 'SMITH'
will not use the index in the field1 field unless you create a function-based index on UPPER(field1).
If a fx-based idx is not feasable (on an older version of Oracle), the trick is to do all operations on the other side of the equation. Like this below is wrong.
select count(*)
from table
where field1 * 10 = parm1 + 4
do this instead:
select count(*)
from table
where field1 = (parm1 + 4) / 10
HTH
Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319
V: 954.484.3191, x174 F: 954.484.2933 C: 954.658.5849
"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."
-----Original Message-----
From: Gillies, Garry [mailto:garry_at_weir.co.uk]
Sent: Tuesday, November 28, 2000 7:01 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL functions and indexes
-----Original Message-----
From: O'Neill, Sean [ mailto:Sean.ONeill_at_organon.ie
<mailto:Sean.ONeill_at_organon.ie> ]
Sent: 28 November 2000 09:46
To: Multiple recipients of list ORACLE-L
Subject: SQL functions and indexes
Last night doing some study for OCP exam I came across a note I had written in course material for PL/SQL, "SQL functions ignore table indexes and therefore can impact on performance.". So now I'm wondering if this is correct or did I take a confused message. Anyone know?
Sean :)
###### ###### ###### ###### # # # # # # # # Rookie #### # # ###### ###### Data # # # # # # # # Base # # ####### ###### # # Adminstrator -------------------------------- ------------Organon (Ireland) Ltd.
Visit: http://www.egroups.com/group/Oracle-OCP-DBA
<http://www.egroups.com/group/Oracle-OCP-DBA>
The only man who never makes a mistake is the man who never does anything.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.comReceived on Tue Nov 28 2000 - 07:38:52 CST
<http://www.orafaq.com>
-- Author: O'Neill, Sean INET: Sean.ONeill_at_organon.ie 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). ++++++++++++++++++++++++++++++++++++ All internet traffic to this site is automatically scanned for viruses and vandals. ++++++++++++++++++++++++++++++++++++ ------_=_NextPart_001_01C05940.8BD1ED86 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: SQL functions and indexes</TITLE>
<META content="MSHTML 5.00.3103.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>This
statement is correct. If you use a function on an indexed field you will not hit the index, unless there is a function-based index on that field.
</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>For
example</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>select
count(*) </SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>from
table</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>where
UPPER(field1) = 'SMITH'</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>will not use the index in the field1 field unless you create a function-based index on UPPER(field1). </SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>If a
fx-based idx is not feasable (on an older version of Oracle), the trick is to do all operations on the other side of the equation. Like this below is wrong.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>select
count(*) </SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>from
table</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>where
field1 * 10 = parm1 + 4</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>do
this instead:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>select
count(*)</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>from
table</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000>where
field1 = (parm1 + 4) / 10</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=780473013-28112000></SPAN></FONT><SPAN class=780473013-28112000><SPAN class=780473013-28112000></SPAN></SPAN> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=780473013-28112000><SPAN
class=780473013-28112000>HTH</SPAN></SPAN></FONT></DIV>
<DIV><SPAN class=780473013-28112000><SPAN
class=780473013-28112000></SPAN> </DIV>
<DIV>
<DIV><SPAN class=780473013-28112000></SPAN></SPAN><B><FONT face=Arial
size=1>Lisa Rutland Koivu</FONT></B> <BR><FONT face=Arial size=1>Oracle Database Administrator</FONT> <BR><FONT face=Arial size=1>Qode.com</FONT> <BR><FONT face=Arial size=1>4850 North State Road 7</FONT> <BR><FONT face=Arial size=1>Suite G104</FONT> <BR><FONT face=Arial size=1>Fort Lauderdale, FL 33319</FONT> </DIV></DIV>
<P><FONT face=Arial size=1>V: 954.484.3191, x174</FONT> <BR><FONT face=Arial
size=1>F: 954.484.2933 </FONT><BR><FONT face=Arial size=1>C: 954.658.5849</FONT>
<BR><FONT face=Arial size=1><A href="http://www.qode.com/"
target=_blank>http://www.qode.com</A></FONT> </P>
<P><I><FONT color=#000000 face=Arial size=1>"The information contained herein
does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."</FONT></I></P>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Gillies, Garry [mailto:garry_at_weir.co.uk]<BR><B>Sent:</B> Tuesday, November 28, 2000 7:01 AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: SQL functions and indexes<BR><BR></DIV></FONT><BR><BR> <P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: O'Neill, Sean [<A href="mailto:Sean.ONeill_at_organon.ie">mailto:Sean.ONeill_at_organon.ie</A>]</FONT> <BR><FONT size=2>Sent: 28 November 2000 09:46</FONT> <BR><FONT size=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT size=2>Subject: SQL functions and indexes</FONT> </P><BR> <P><FONT size=2>Last night doing some study for OCP exam I came across a note I had written</FONT> <BR><FONT size=2>in course material for PL/SQL, "SQL functions ignore table indexes and</FONT> <BR><FONT size=2>therefore can impact on performance.". So now I'm wondering if this is</FONT> <BR><FONT size=2>correct or did I take a confused message. Anyone know?</FONT> </P> <P><FONT size=2>Sean :)</FONT> </P> <P><FONT size=2>###### ###### ###### ###### </FONT><BR><FONT size=2># # # # # # # # Rookie</FONT> <BR><FONT size=2>#### # # ###### ###### Data</FONT> <BR><FONT size=2># # # # # # # # Base </FONT><BR><FONT size=2># # ####### ###### # # Adminstrator</FONT> <BR><FONT size=2>-------------------------------- ------------ </FONT><BR><FONT size=2>Organon (Ireland) Ltd.</FONT> <BR><FONT size=2>E-mail: sean.oneill_at_organon.ie [subscribed: Digest Mode]</FONT> </P> <P><FONT size=2>Visit: <A href="http://www.egroups.com/group/Oracle-OCP-DBA" target=_blank>http://www.egroups.com/group/Oracle-OCP-DBA</A></FONT> </P> <P><FONT size=2>The only man who never makes a mistake is the man who never does anything.</FONT> <BR><FONT size=2> - Theodore Roosevelt</FONT> </P> <P><FONT size=2>-- </FONT><BR><FONT size=2>Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com</A></FONT> <BR><FONT size=2>-- </FONT><BR><FONT size=2>Author: O'Neill, Sean</FONT> <BR><FONT size=2> INET: Sean.ONeill_at_organon.ie</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> <BR><FONT size=2>++++++++++++++++++++++++++++++++++++</FONT> <BR><FONT size=2>All internet traffic to this site is </FONT><BR><FONT
 |
 |