RE: function based indexes.

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 21 Feb 2003 12:35:51 -0800
Message-ID: <F001.00555447.20030221123551@fatcity.com>

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-----
Sent: Friday, February 21, 2003 2:55 PM
To: Multiple recipients of list ORACLE-L

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Fri Feb 21 2003 - 14:35:51 CST