Home » SQL & PL/SQL » SQL & PL/SQL » SQL query to calculate distance and update (Oracle 11.2.0.3)
| SQL query to calculate distance and update [message #630223] |
Tue, 23 December 2014 05:06  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
I have two tables having millions of rows in each table i.e. t_src_dim and t_trgt_dim.
t_src_dim stores name of the car owner and his location in terms of latitude and longitude.
t_trgt_dim stores latitude and longitude of the service center.
Now the task is, for each record in t_src_dim, identify the nearest service center from t_trgt_dim using lat, lon columns of both the tables
and update the t_src_dim tabl.flag to 'Y' if any service center is in the limit of 20 miles and update it to 'N' if there is no service center in the limit of 20 miles.
function msr_dstnc calculates (just for e.g.) the distance in miles for given set of latitude and longitude.
create table t_src_dim (owner varchar2(20), lat number, lon number, flag varchar2(20))
/
begin
insert into t_src_dim values ('a',1, 2,'');
insert into t_src_dim values ('b',1, 0,'');
insert into t_src_dim values ('c',1, 5,'');
insert into t_src_dim values ('d',4, 6,'');
insert into t_src_dim values ('e',8, 4,'');
insert into t_src_dim values ('f',9, 4,'');
insert into t_src_dim values ('g',1, 1,'');
end;
/
create table t_trgt_dim (service_center varchar2(40), lat number, lon number)
/
begin
insert into t_trgt_dim values ('carz',5, 1);
insert into t_trgt_dim values ('cars heaven',1,2 );
insert into t_trgt_dim values ('your car',1, 2);
insert into t_trgt_dim values ('yourcar center',1, 2);
insert into t_trgt_dim values ('abcd',1, 1);
insert into t_trgt_dim values ('new car center',1, 1);
end;
/
CREATE OR REPLACE function msr_dstnc(a number, b number, c number, d number)
return number
as
begin
return a*b*c*d;
end;
/
Below query is serving the purpose but I would like to know if there is a better way doing it interms of performance.
I would like to stop processing of the row from the t_src_dim table once any row from t_trgt_dim is found in the distance of 20 miles, I've thought in PLSQL but is it possible in SQL.
UPDATE t_src_dim a
SET flag =
(SELECT flg
FROM (SELECT a.lat, a.lon,
MAX (CASE
WHEN msr_dstnc (a.lat, a.lon, b.lat, b.lon) < 20
THEN 'Y'
ELSE 'N'
END
) flg
FROM t_src_dim a, t_trgt_dim b
GROUP BY a.lat, a.lon) t
WHERE t.lat = a.lat AND t.lon = a.lon)
Regards,
Pointers
[Updated on: Tue, 23 December 2014 05:08] Report message to a moderator |
|
|
|
|
|
| Re: SQL query to calculate distance and update [message #630233 is a reply to message #630230] |
Tue, 23 December 2014 06:39  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi Micheal,
Thank you for your response.
I am not sure if our client can take the option of buying something.
However, the function which I am using is a sample function, in fact, the actual function is bit more complex in logic therefore I used the function to cover-up the code and its used in other areas as well.
Regards,
Pointers
|
|
|
|
|
|
| Re: SQL query to calculate distance and update [message #630249 is a reply to message #630223] |
Tue, 23 December 2014 12:03 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Scrap your function as Michel suggested. Get rid of group by. Compare:
explain plan for
UPDATE t_src_dim a
SET flag =
(SELECT flg
FROM (SELECT a.lat, a.lon,
MAX (CASE
WHEN a.lat * a.lon * b.lat * b.lon < 20
THEN 'Y'
ELSE 'N'
END
) flg
FROM t_src_dim a, t_trgt_dim b
GROUP BY a.lat, a.lon) t
WHERE t.lat = a.lat AND t.lon = a.lon)
/
select * from table(dbms_xplan.display)
/
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2591588453
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 7 | 266 | 52 (14)| 00:00:01 |
| 1 | UPDATE | T_SRC_DIM | | | | |
| 2 | TABLE ACCESS FULL | T_SRC_DIM | 7 | 266 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 29 | 6 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 52 | 6 (0)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN| | 1 | 52 | 6 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
|* 6 | TABLE ACCESS FULL | T_SRC_DIM | 1 | 26 | 3 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 6 | 156 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T_TRGT_DIM | 6 | 156 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("A"."LAT"=:B1 AND "A"."LON"=:B2)
Note
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
24 rows selected.
SQL>
and
explain plan for
UPDATE t_src_dim a
SET flag = CASE
WHEN EXISTS (SELECT 1 FROM t_trgt_dim b WHERE b.lat * b.lon * a.lon * b.lat < 20) THEN 'Y'
ELSE 'N'
END
/
select * from table(dbms_xplan.display)
/
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 244025122
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 7 | 175 | 31 (23)| 00:00:01 |
| 1 | UPDATE | T_SRC_DIM | | | | |
| 2 | TABLE ACCESS FULL| T_SRC_DIM | 7 | 175 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_TRGT_DIM | 1 | 26 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."LAT"*"B"."LON"*:B1*"B"."LAT"<20)
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected.
SQL>
And you can improve it even more by creating FBI on t_trgt_dim:
SQL> create index t_trgt_dim_idx1
2 on t_trgt_dim(
3 lon * lat
4 )
5 /
Index created.
SQL> explain plan for
2 UPDATE t_src_dim a
3 SET flag = CASE
4 WHEN a.lat * a.lon = 0 then 'Y'
5 WHEN EXISTS (SELECT 1 FROM t_trgt_dim b WHERE b.lat * b.lon < 20 / a.lon * a.lat) THEN 'Y'
6 ELSE 'N'
7 END
8 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 1366157097
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 7 | 266 | 17 (42)| 00:00:01 |
| 1 | UPDATE | T_SRC_DIM | | | | |
| 2 | TABLE ACCESS FULL| T_SRC_DIM | 7 | 266 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_TRGT_DIM_IDX1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LON"*"LAT"<20/:B1*:B2)
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 19:43:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
