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.

Sunday, December 03, 2006

Loud! 2006 - Our own OOW, but smaller…

On November 9th my employer, LogicaCMG, organised it’s own version of Oracle Open World, but just a little bit smaller. About 130 colleagues came to Amstelveen to listen to the keynote (after a good dinner) and after that three times a choice from one of the three parallel sessions. As usual the evening was very well organised (thank you committee!), but there was some experience as it was already the 7th version of this annual event.
The keynote speaker was Rob Blaauboer who enlightened us about Innov8 (the 8 should be the mathematical infinite sign, but I can’t find that one on my keyboard), a method to visualize what technologies a company should adopt or look in to in the nearer or farther future.

The first parallel session I visited was Oracle2Go by Reza and Robert. Oracle2GO is a framework as an extension of the ADF Framework, to develop applications faster and with higher quality. They mentioned a couple of success stories and proudly pointed out that they managed to achieve a productivity of 4 hours per function point (calculated according to the fpa standards of the Dutch NESMA): a productivity that is barely to improve in the ‘old fashioned” Designer/Developer approach!

After that Lex Borger told us about Identity and Access Management: he explained the problems around AIM and pointed out the different alternatives for solving these problems. Finally – as this was an Oracle related event – he mentioned the solutions Oracle offers.

The last session I choose was The Best of Breed of Fusion by Ivan Pellegrin. He stated that the Oracle product range is so gigantic nowadays, that the time you could know everything about all Oracle products (as in the time there was only a database, Forms 2.3 and RPT/RPF) has been long gone. So we have to choose our interests and specialise in that area. Form his point of view BI Publisher and WebCenter will get a lot of attention in the near future.

The evening was closed with a quiz (a sort of Jeopardy or 1-against-100) where the winner, he who knows the most (or guessed best) about Oracle related questions, received a Playstation2.

All in all it was a great evening and I’m already looking forward to the next one!