Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical SQL problem
Hierarchical SQL problem [message #244015] Mon, 11 June 2007 05:28 Go to next message
gags_78
Messages: 1
Registered: June 2007
Junior Member
Hey folks,

Our system generates a number of alerts and logs them to a databse. The table structure corresponding to the alerts is as follows :-

create table ALERTS (
alert_id number PRIMARY KEY ,
parent_id number,
creation date NOT NULL,
message varchar(100) NOT NULL
);

the parent_id field refers to another alert row in the table that came before it but is effectively just a repeat/duplicate of the alert that occurred within the last 10 minutes.

Currently we mark the various parent_ids in duplicate alerts with the following sql :-

update alerts a set parent_id = (
select max(p.alert_id) from alerts p
where p.alert_id < a.alert_id
and p.message = a.message
and p.creation > a.creation - interval '10' second)


and then in order to find the root alert i.e. the very first instance of the alert we have the following hierarchical sql statement:-

select alert_id, parent_id, connect_by_root alert_id as root_id from alerts
where creation > trunc(sysdate - interval '1' day)
start with parent_id is null
connect by parent_id = prior alert_id


We feel this is rather convoluted for what initially seems like a fairly straight forward problem, firstly simply mark any duplicate alert messages parent_id with the original alerts id and then return the root alert_id for a given alert.

Would anyone have encountered such a problem before and perhaps be able to suggest an easier/simpler/cleaner way of doing this?

Thanks,
Mark.


Re: Hierarchical SQL problem [message #244423 is a reply to message #244015] Tue, 12 June 2007 14:15 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I would just change the table to
CREATE TABLE alerts (
  alert_id  NUMBER PRIMARY KEY ,
  parent_id NUMBER,
  root_id   NUMBER,
  creation  DATE NOT NULL,
  message   VARCHAR(100) NOT NULL
);
and change the INSERT statement for each new alert to query the parent_id and the root_id based on your criteria. You are currently updating all your parent_ids every time you run your update command.
Previous Topic: Finding 15th of Prior Month
Next Topic: Multiple Insert, Internal Query and Static Value
Goto Forum:
  


Current Time: Sun Dec 04 14:43:31 CST 2016

Total time taken to generate the page: 0.10142 seconds