| 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
Hi Sean,
Probably what you were referring to was that Oracle won't use an index if
you use a function in the WHERE clause of a statement.
For example:
SELECT Name FROM Emp WHERE LENGTH( Name ) < 4;
(i.e., find all people whose name is shorter than 4 letters - why you want
to do this, I don't know, I'm just using it for the example - maybe you are
trying to find data capture errors as names can't be three letters or less)
In a case like this, the data is being 'massaged' by the function, and therefore Oracle cannot use the index for restricting which rows are selected (because indexes in Oracle 7 order the data in numerical or alphabetical order - not in the order of the result of the function).
So as Oracle MUST look at EVERY record in the table to pass the value of the field to the function to see if the result needs to be included, Oracle must do a full table scan - so it ignores the index. Using the index would be more inefficient (and may in fact skip some rows which should be included (if they have a null value for the indexed field)), as it would first have to read the index, then read all the rows of the table anyway.
By avoiding the index, Oracle must do a full table scan. Therefore it cannot use the index to restrict the rows. So it can have an adverse effect on the performance! Imagine if you wanted to find any employees who had a name of three letters or less. You couldn't use an index on the name field - it would be meaningless to process the table in alphabetical order, and you would skip anybody who didn't have a name. If there were only 17 people out of a table of 100 million records, you can see that having to do the full table scan to find the 17 rows could be considered inefficient!
Having said that, Oracle 8 now DOES allow an index of the result of a function - so you could have an index on the LENGTH( name ) field. Then you could use it in your WHERE clause, and in the above example, would only return the 17 rows you wanted - which would be efficient!
Hope this clarifies the situation, or jogs your memory!
Regards
Oweson Flynn
EMail: oef_at_icon.co.za
----- Original Message -----
From: "O'Neill, Sean" <Sean.ONeill_at_organon.ie>
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Tuesday, November 28, 2000 11:46 AM
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.
> E-mail: sean.oneill_at_organon.ie [subscribed: Digest Mode]
>
> Visit: http://www.egroups.com/group/Oracle-OCP-DBA
>
> The only man who never makes a mistake is the man who never does anything.
> - Theodore Roosevelt
>
> --
> Please see the official ORACLE-L FAQ: 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).
>
This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. Received on Tue Nov 28 2000 - 05:52:04 CST
 |
 |