I have written the query which follows to show the difference between
succesive
timestamps. The definition of the table in question follows as well.
The table has somewhat over 4 million rows, and there are between 0 and
200 or so rows for each minute, although some minutes might see 1000 to
1500 rows.

The query is taking around 6 minutes to run. If I remove the "order by
b.id",
it takes 10 seconds, but then, of course, sometimes it gives the correct
answer and
sometimes it doesn't. I had inadvertently left out the order by when the
table was
much smaller and the query worked. I'm thinking that worked at first
because
the rows are added in timestamp order and the ID field is gotten from a
generator,
so perhaps "natural order" was breaking my way. As the table has gotten
larger
the query results were clearly wrong and I realized I needed to order the
subselect.

"a.TSTAMP + .007" is an attempt to limit the size of the subselect; that's
about
10 minutes which is much longer than any idle time is likely to be. This
did help
a little, but not enough.

The plan (which I can't generate just now because I don't have access to the
database at this moment) uses the primary key and the index on TSTAMP.
It seems to be the sort on the subselect that's killing me. Is there a
better way to
do do this?


Here's the query:

select a.id, s.SSN, CAST(a.tstamp AS TIME) as "Created in MSGRTR",
cast(((select first 1 b.tstamp from dq b join SSNT t
on b.SSN_ID = t.SSN_ID
and b.TSTAMP between a.TSTAMP and a.TSTAMP + .007
and t.TID = 'D'
and b.DIR = 'I'
where b.id <> a.id order by b.id ) - a.TSTAMP) * 86400
as numeric(8,3)) as "Interval" from dq a join SSNT s
on a.SSN_ID = s.SSN_ID
where a.TSTAMP between '16-dec-2008 18:00' and '16-dec-2008 19:30'
and s.TID = 'D'
and a.DIR = 'I'

And here's the table:

CREATE TABLE DQ
(
ID Bigint NOT NULL,
DIR Char(1),
LINE Varchar(16),
SEQ Bigint,
ASN Varchar(12),
MT Varchar(3),
TXTSIZE Integer,
TSTAMP Timestamp,
SSN_ID Bigint,
CONSTRAINT PK_DQ_1 PRIMARY KEY (ID)
);

ALTER TABLE DQ ADD ANAME COMPUTED BY (substring(asn from 1 for 4));
ALTER TABLE DQ ADD CONSTRAINT FK_DQ_MIR
FOREIGN KEY (MIR_ID) REFERENCES MIRT (MIR_ID) ON UPDATE CASCADE;
ALTER TABLE DQ ADD CONSTRAINT FK_DQ_SSN
FOREIGN KEY (SSN_ID) REFERENCES SSNT (SSN_ID) ON UPDATE CASCADE;
CREATE INDEX IDX_TSTAMP ON DQ (TSTAMP);




Michael D. Spence
Mockingbird Data Systems, Inc.



[Non-text portions of this message have been removed]