I'm not used to hierarchical queries, so I would like some help here.
Oracle 10. Let's figure a table which records the change of status of some items in a log way. This items are linked in a parent-son way, but very limited, as a parent can have just one son.
This is the sample:
create table item_log (
id number(1),
status varchar2(10),
date_changed date
);
insert into item_log values (1, 'NEW', to_date('01/01/2009','DD/MM/YYYY'));
insert into item_log values (1, 'WORN OUT', to_date('12/01/2009','DD/MM/YYYY'));
insert into item_log values (2, 'NEW', to_date('12/01/2009','DD/MM/YYYY'));
insert into item_log values (2, 'WORN OUT', to_date('20/01/2009','DD/MM/YYYY'));
insert into item_log values (3, 'NEW', to_date('20/01/2009','DD/MM/YYYY'));
insert into item_log values (4, 'NEW', to_date('22/01/2009','DD/MM/YYYY'));
insert into item_log values (4, 'WORN OUT', to_date('28/01/2009','DD/MM/YYYY'));
insert into item_log values (5, 'NEW', to_date('28/01/2009','DD/MM/YYYY'));
insert into item_log values (5, 'WORN OUT', to_date('31/01/2009','DD/MM/YYYY'));
All the items have one and just one NEW status record, but not all of them have to be neccesarily replaced. They can just die like id 5 or keep working like id 3.
I am looking for a good way to get, for a given id, all the chain of parents and sons backward and forward. From the top ancestor to my id, from my id to the last descendant, and also the full chain.
-----
<h3>What I have been trying:</h3>
(you can skip this part if you feel confident enough with information I have provided)
I thought a good beginning would be to be able to build a query which gets the top ancestor of any id, something like this:
ID TOP_ANCESTOR
-- ----
1 NULL
2 1
3 1
4 NULL
5 4
I'm messing around with the connect by query and the new connect_by_root utility, but I'm not getting to deeply understand it. This is my first query:
SELECT ID, status, date_changed, CONNECT_BY_ROOT ID, LEVEL
FROM item_log
CONNECT BY PRIOR date_changed = date_changed
AND PRIOR status = 'WORN OUT'
START WITH status = 'NEW';
I was expecting this query would return something like what I wanted, but it obviously doesn't work.
Then I tried this other approach:
This query links the ids with their immediate parent:
Select NEW.*, WORN.id parent
From item_log NEW, item_log WORN
Where new.status = 'NEW' and WORN.status(+) = 'WORN OUT'
and NEW.date_changed = WORN.date_changed(+)
So I can use it as a subquery for a "connect by" hierarchical query:
Select LOG.*, connect_by_root id, level
From
(Select NEW.*, WORN.id parent
From item_log NEW, item_log WORN
Where new.status = 'NEW' and WORN.status(+) = 'WORN OUT'
and NEW.date_changed = WORN.date_changed(+)) LOG
Connect by prior id = parent
start with parent is null;
ID STATUS DATE_CHANGED PARENT CONNECT_BY_ROOTID LEVEL
1 NEW 01/01/2009 1 1
2 NEW 12/01/2009 1 1 2
3 NEW 20/01/2009 2 1 3
4 NEW 22/01/2009 4 1
5 NEW 28/01/2009 4 4 2
This query does work as I expected ... but when I apply it to the real data (50.000 records and one more related column) ... it takes a hell of execution time!! (I had to cancel it before risking the dba coming to my desk and having me sent to the coders dungeon :) ).
After seeing this, I forgot about hierarchical and changed to a more straight forward approach. I built a step-by-step function which gets the parent of the current id and recursively calls itself until finds no parent, retrieving thus the oldest ancestor.
From here on it is easy to make a function which returns a collection of records with all the parent-son chain, but I am not satisfied. I find this approach a bit too much straightforward, and something tells me that there must be a better / faster / simpler way to achieve this.
Any idea?
Thanks.
Edited by: julius on Nov 17, 2009 4:31 AM