| function based index [message #2479] |
Fri, 19 July 2002 07:38  |
pavani
Messages: 32
Registered: April 2002
|
Member |
|
|
hi
i'm going through performance tunning.in that i came across function based index..and i don't know what it is.so,can any one plzz clarify my doubt.plz tell me what it is and whar for it is used and where it is used if possible plzz explain me with an example and i appreciate u .
thank u
|
|
|
|
| Re: function based index [message #2488 is a reply to message #2479] |
Fri, 19 July 2002 10:27  |
ctg
Messages: 146
Registered: July 2002
|
Senior Member |
|
|
normal indexes simply store the column value specified:
CREATE INDEX emp_ename_idx ON emp(ename);
Function based indexes store the result of a function or expression in the index:
CREATE INDEX emp_upper_ename_idx ON emp(UPPER(ename));
Where the benefit is to come in is when you write a query like this:
SELECT ...
FROM emp
WHERE upper(ename) = 'KING';
Without the function based index, oracle has always had to do a tablespace scan. But with the function based index, now oracle can retrieve the data using the index (ideally, but does not always seem to work).
hope this helps
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
