RE: function based indexes.

[Jamadagni, Rajendra]

Title: RE: Oh Where Oh Where Is My Redo Coming From

Chris, I could be wrong, but I was under impression that parameter to the F in FBI needed to be either hard coded values or db table columns. I don't think they could be variables.   But then again I have been wrong ...   Raj ------------------------------------------------------------- Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -----Original Message----- From: Chris Stephens [mailto:[EMAIL PROTECTED] Sent: Friday, February 21, 2003 2:55 PM To: Multiple recipients of list ORACLE-L Subject: function based indexes. I don't think this is possible but I would like to confirm here. I would like to create a function based index to speed up the OH SO SLOW query that follows:   SELECT dmzu.ZIP_CODE       FROM dm_zip_unq dmzu       WHERE fnc_dist(some number,some number,dmzu.LATITUDE,dmzu.LONGITUDE)< power(100,2) )     Here is the function definition:   CREATE OR REPLACE FUNCTION fnc_Dist (          inp_lat DM_ZIP_UNQ.LATITUDE%TYPE,          inp_lng DM_ZIP_UNQ.LONGITUDE%TYPE,          dl_lat  DM_ZIP_UNQ.LATITUDE%TYPE,          dl_lng  DM_ZIP_UNQ.LONGITUDE%TYPE)          RETURN NUMBER IS            BEGIN                   RETURN (POWER((69.1*(dl_lng - inp_lng ) * COS(inp_lat / 57.3)), 2) + POWER((69.1*(dl_lat - inp_lat)),2));            END fnc_Dist; /     ....with those first 2 parameters, I won't be able to create the function based index correct?  Any ideas on how to alter the design of the query?   The idea is to provide the functionality to locate a dealer of a certain product when given a certain longitude and latitude. I pretty much know the answer to my question but am looking for any and all design suggestions.   Thanks.  BEER ME! chris

********************************************************************This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*********************************************************************2