Tuesday, December 05, 2006

Creating a network planning using hierarchical queries

Assume we have a table of locations like
Name                                      Null?    Type
----------------------------------------- -------- -----------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(50)
and a table of possible lines (or route parts) like
Name                                      Null?    Type
----------------------------------------- -------- --------
ID NOT NULL NUMBER
FROM_LOC_ID NOT NULL NUMBER
TO_LOC_ID NOT NULL NUMBER
DISTANCE NOT NULL NUMBER
How can we calculate the best route (in this case: the route with the shortest distance) from a startpoint to an endpoint?
The answer is: By using an hierarchical query:
select dep_name
, arr_name
, route
, distance
, execute(distance) total
from
(
select connect_by_root dep.id dep_id
, connect_by_root dep.name dep_name
, arr.id arr_id
, arr.name arr_name
, level stops
, substr(sys_connect_by_path(dep.name||'-'||arr.name, '->'),3) route
, substr(sys_connect_by_path(lne.id, ','),2) lne_ids
, substr(sys_connect_by_path(lne.distance, '+'),2) distance
from lines lne
, locations dep
, locations arr
where lne.from_loc_id = dep.id
and lne.to_loc_id = arr.id
connect by nocycle prior arr.id = dep.id
)
where dep_name = 'MAASTRICHT'
and arr_name='AMSTERDAM'
ORDER BY 5 ASC
(Remark: The function 'execute' does nothing but returning the result of an execute immediate of a string value).
The outcome of this query is a list of all defined routes from Maastricht to Amsterdam with the shortest route shown first. The
"substr(sys_connect_by_path(dep.name||'-'||arr.name, '->'),3) route"
column returns the route like
"MAASTRICHT-EINDHOVEN->EINDHOVEN-UTRECHT->UTRECHT-AMSTERDAM"
The column
"substr(sys_connect_by_path(lne.distance, '+'),2)"
returns a string that represents the distances to sum:
"86,7+93,6+44,7"
This column is used for calculating the total distance.

So you can use hierarchical queries for a lot more purposes than the obvious who-is-the-manager query.
Ofcourse you won't use this kind of solution to calculate distances (there are far better webservices available for that). In 'real life' we've used this approach (but more elaborated and a lot more difficult) to get the cheapest supplier for the movement of goods from a to b.
Post a Comment