building a function based index oracle sql

Question

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

ive tried using: CREATE INDEX Idx ON sightings sqrt(power(lat - latitude, 2) + power(lon - longitude, 2)); except its unable to see the params defined in the with statement — 101ldaniels
– 101ldaniels, Commented Oct 23, 2016 at 7:00

Michael Piankov · Accepted Answer · 2016-10-23 07:26:18Z

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

Collectives™ on Stack Overflow

building a function based index oracle sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related