Hello all,

I use a temporary table to store a time interval like (start_timestamp, length in second), and becuase some periods can be overlaped, I need to truncate interval lengths. It seems to be very easy, but I don't get an acceptable performance. So, I hope, some have some tip for me.

drop table if exists tt
/

create local temporary table tt (period_start timestamp not null, period_len numeric(10,0), primary key (period_start))
not transactional
/

begin
    declare @vi     integer;
    declare @vt     timestamp;

delete from tt;

set @vi = 20000;
    while @vi > 0 loop
        set @vt = dateadd(second, truncnum(365*24*60*60 *rand(), 0), '2010-01-01' );
        insert into tt (period_start, period_len)
        on existing skip
        values (@vt, truncnum(60*60*rand(), 0));
        set @vi = @vi - 1;
    end loop;

insert into tt (period_start, period_len)
    on existing skip
    select dateadd(second, truncnum(60*rand(), 0), period_start), period_len - 60
    from tt
    where period_len > 60
    order by period_start, period_len;
    commit;
end;
/

select count(*) from tt
/

-- bad plan, because s2 not limited to a first row --
select s1.period_start, coalesce(min(s2.period_start), s1.period_start) as next_start
from tt s1, tt s2
where s2.period_start > s1.period_start
group by s1.period_start 
/

-- that statement is equal to the line above, but is not acceptable in my case --
select period_start, last_value(period_start) over (order by period_start rows between current row and 1 following) as next_start
from tt
/