comparisons over a DB link

[uparna]

Ok , here is the situation:-


I have a set of records in a table , say table OLD_DELIVERIES (this contains all the houses to which we have delivered flowers in the past)

House#       Lat 		Long        						
H1	     17.15000000	-61.81666667	   Row_1	
H2           37.85000000	144.75000000	 Row_2
H3           -23.35000000	117.68333333	Row_3
H4           -20.01666667	148.23333333	Row_4
H5           -31.93333333	115.83333333	Row_5
H6           -41.56666667	148.16666667	Row_6

and another table

table EXISTING_DELIVERIES (This contains the list of houses which we are delivering flowers to today)

delivery#    Lat 		Long
D55	     19.50000000	-80.66666667	  Row_A
D56          -23.65000000	-70.40000000    Row_B
D57          -51.26666667	-72.35000000    Row_C
D58          -52.78333333	-69.28333333    Row_D
D59          -32.70000000	-71.21666667    Row_E
D60          -26.56666667	-69.06666667    Row_F
D61          -33.13333333	-71.35000000	Row_G

Now , i have to do this:-
For all the deliveries that i have to do today, i have to find houses which are within 10 miles of my current delivery, and to which i have delivered to before --- that means, if i am delivering to house B today, which is next to house A (to which i have delivered flowers to in the past) , then i would like the truck to stop over at house A and give them a complimentary bunch of roses, for being our past customers.

All this is based on Lat Long -- i use the haversign function to calculate the distance (FN_HAVERSIGN takes lat1, long1, lat2 , long2 as input parameters and gives me distance back).

i have given only 6 records here , but my past delivery table has more than 100,000 records , and my current delivery table has more than 4-5000 records. As you can see , with full tablescans taking place on these long and wide tables (-- i havent mentioned all columns here as they are not impt for calculation), the time taken for this comparison is too much -- kind of like 4 seconds per old delivery ( - 400,000 secs every time i want to find old houses near to my delivery sites)

Row_A has to be compared to row_1,row_2...6 ; similarly Row_B has to be compared to row_1,row_2...6 so we have to do 6x7 = 42 comparisons here and then find if any of the distances here are below 10.


I tried creating an FBI on EXISTING_DELIVERIES(FN_HAVERSIGN(0,0,lat,long)) but couldnt do it as the function wasnt deterministic. duh!

Any ideas what to do? I have already tried creating a table of records for this , and the performance is not worth singing about. To add insult to injury , the OLD_DELIVERIES table is in another database which we access over a DB_link -- we can read all records pretty fast over the DB link , but try doing FTSs over the DB link , and you can reschedule your appointments for the day and sit in front of the PC.

Thanks for reading!

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jay,

One method to improve access speeds would be to use Oracle Partitions to physically group your Longitudes/Latitudes. Then the records that would be candidates for consideration (and distance calculation) are those few records which qualify by virtue of being within (+/-) 10 miles of longitude or latitude of your origin point. That should produce virtually instantaneous results.

Let us know your thoughts on this strategy.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.