sql - Joining 2 tables to calculate the time difference -

we have couple of seperate event tables. given id know time difference between 2 events. now, there can several of same events given id, interested in "next" event.

example:

http://sqlfiddle.com/#!4/1f724/6/0

schema

create table event_a (   timestmp timestamp(3),   id       varchar2(50) );  create table event_b (   timestmp timestamp(3),   id       varchar2(50) );  insert event_a values (to_timestamp('2015-05-12 10:22:00', 'yyyy-mm-dd hh24:mi:ss'), 'x'); insert event_b values (to_timestamp('2015-05-12 10:22:05', 'yyyy-mm-dd hh24:mi:ss'), 'x'); insert event_a values (to_timestamp('2015-05-12 10:22:08', 'yyyy-mm-dd hh24:mi:ss'), 'x'); insert event_b values (to_timestamp('2015-05-12 10:22:10', 'yyyy-mm-dd hh24:mi:ss'), 'x'); 

query

this query came with, seems me little complex. wondering if there better way this.

this query takes long time run if there lot of data in these tables.

select a.id, nvl((extract(day (b.timestmp - a.timestmp))   * 86400                  + extract(hour (b.timestmp - a.timestmp))   * 3600                   + extract(minute (b.timestmp - a.timestmp)) * 60                     + extract(second (b.timestmp - a.timestmp)) ), 0) duration_in_sec event_a join event_b b on a.id = b.id , b.timestmp = (   select min(timestmp)   event_b   id = a.id   , timestmp >= a.timestmp ) 

this solution should faster not have self-join subquery:

select id, extract(day (d)) * 86400 + extract(hour (d)) * 3600           + extract(minute (d)) * 60  + extract(second (d)) duration   (     select a.id, b.timestmp - a.timestmp d,         row_number() on (partition a.id, a.timestmp order b.timestmp) rn       event_a        join event_b b on a.id = b.id , a.timestmp <= b.timestmp       order a.timestmp)   rn = 1 

sqlfiddle