Michael,

>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.

It has nothing to do with "natural order", a resultset doesn't -have- a
natural order. Without an ORDER BY clause, the result isn't sorted
in any shape or form.

> "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?

What is the Query Plan?

> 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);

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com