Xref: alice comp.databases.oracle.server:36855 Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!logbridge.uoregon.edu!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.gtei.net!inet16.us.oracle.com!not-for-mail From: tkyte@us.oracle.com (Thomas Kyte) Newsgroups: comp.databases.oracle.server Subject: Re: user-defined functions Date: Fri, 01 Jan 1999 14:48:56 GMT Organization: Oracle Service Industries Lines: 65 Message-ID: <368ede6d.6306848@192.86.155.100> References: <76catd$d4t$1@news.kren.nm.kr> <369009b3.3559585@netnews.worldnet.att.net> <368C481F.6293@teleport.com> Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Newsreader: Forte Agent 1.5/32.451 A copy of this was sent to GHouck (if that email address didn't require changing) On Thu, 31 Dec 1998 19:59:27 -0800, you wrote: >Jonathan Gennick wrote: >> >[snip] [snip] >> >> If there is an index on emp_name, Oracle will likely use it. >> Here is the same query again, but with the UPPER function >> being used to make it case insensitive: >> >> select * from employee >> where UPPER(emp_name) = 'GENNICK'; >> >> In this case, Oracle can't use an index. In order to >> evaluate UPPER(emp_name) properly, it must apply that >> function to each row in the table. That means a full table >> scan, where every record is read. >> >[snip] > >I have wondered why this is the case. Why doesn't Oracle >use the UPPER(...) function on the key values in the >index; perhaps slower, but avoiding the full-table? > you cannot use the index in this case. You would have to read the entire index since using a function on the indexed columns would change the sort order (so the index would not be sorted by the UPPER(ename). the optimizer looks at the above query and sees it would have to read the full index and then do a table access by rowid anyway (it would have to go to the table to get the "select *") so it full scans. As far as the optimizer knows, upper(emp_name) might return 'GENNICK' for all rows, hence more then 20% of the table would be accessed which is another cut off for index usage. In Oracle8i, you will be able to index UPPER(ename) (you can index functions) and so it will be possible to use indexes when you use a function on a column (given you indexed that function on that column). >Thanks, > >Geoff Houck >systems hk >hksys@teleport.com >http://www.teleport.com/~hksys Thomas Kyte tkyte@us.oracle.com Oracle Service Industries Reston, VA USA -- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.