101ldaniels 101ldaniels - 1 month ago 8
SQL Question

building a function based index oracle sql

for a query like this:

WITH params as (SELECT -28 as lat, 151 as lon
FROM dual)
SELECT sighting_id, sqrt(power(lat - latitude, 2) + power(lon - longitude, 2))

AS distance FROM sightings CROSS JOIN params

WHERE sqrt(power(lat - latitude, 2) + power(lon - longitude, 2)) =

(SELECT MAX(sqrt(power(lat - latitude, 2) + power(lon - longitude, 2)))

FROM sightings CROSS JOIN params);


how exactly would i go about building a function based index on it? sighting_id is the primary key, does this mean id have to build it on the long and lats? ive looked a up some examples but none really seem to show me how id go about building one here.

the execution plan is as follows:

id operation name
0 select statement
1 table access full sighting
2 sort aggregate
3 table access full sighting

Answer

As I understad you need to build index by sqrt(power(lat - latitude, 2) + power(lon - longitude, 2)) and root cause of problem is there is columns from different table. But in your example lat always "-28" and lon = "151". And you may rewrite query to

SELECT sighting_id
     , sqrt(power(-28 - latitude, 2) + power(151 - longitude,  2))  AS distance 
FROM sightings 
WHERE sqrt(power(-28 - latitude, 2) + power(151 - longitude, 2)) =
(SELECT MAX(sqrt(power(-28 - latitude, 2) + power(151 - longitude, 2)))
FROM sightings ); 

and create FB indnex

create index I_sightings_distance on sightings (sighting_id
     , sqrt(power(-28 - latitude, 2) + power(151 - longitude,  2)) ); 

I add sighting_id for instnace. It's indeax are able to use in fast full scan.

NB. there is some restiontions to use FBIndex https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm#SQLRF01212