| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL: Working with huge tables of chronological data
On Apr 6, 11:06 am, "John" <acide.ascorbi..._at_gmail.com> wrote:
> On Apr 5, 5:07 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > What can we do with the above to avoid the Cartesian join as much as
> > possible? We can use LEAD to peek at the next set of values:
> > SELECT
> > TO_CHAR(TIME_DATE,'HH24:MI') TIME_DATE,
> > TO_CHAR(LEAD(TIME_DATE,1) OVER (ORDER BY TIME_DATE),'HH24:MI')
> > NEXT_TIME_DATE,
> > TIME_SLOT,
> > LEAD(TIME_SLOT,1) OVER (ORDER BY TIME_DATE) NEXT_TIME_SLOT,
> > T1_V1,
> > T1_V2
> > FROM
> > (SELECT
> > NVL(T1.V1,T2.V1) TIME_DATE,
> > DECODE(T1.V1,NULL,'B',NVL2(T2.V1,'AB','A')) TIME_SLOT,
> > TO_CHAR(T1.V1,'HH24:MI') T1_V1,
> > T1.V2 T1_V2,
> > TO_CHAR(T2.V1,'HH24:MI') T2_V1,
> > T2.V2 T2_V2
> > FROM
> > T1 FULL OUTER JOIN T2 ON T1.V1=T2.V1
> > ORDER BY
> > NVL(T1.V1,T2.V1));
>
> Wouo thanks for such a big and detailed answer!
>
> However I think this query only search for consecutive A pairs. As in
> the previous post, if I have:
>
> Chronology
> (03:50pm, A1)
> (03:51pm, B1)
> (03:55pm, A2)
> (03:58pm, A3)
> (03:59pm, A4)
> (04:00pm, B2)
>
> I'm looking for all the sequences of events A in the chronology with
> no B event in the middle. The results would be:
> (A2, A3) ; (A3, A4) and also (A2, A4) even if A2 and A4 are not
> consecutive.
>
> Thanks for helping!
>
> John
At first glance, it appears to be only giving consecutive pairs, but that is not the case. The input looks like this, from a previous step in the build of the final SQL statement:
05-APR-2007 00:02:00 05-APR-2007 00:05:00 1 A A 00:02 1 05-APR-2007 00:05:00 05-APR-2007 00:07:00 2 A A 00:05 2 05-APR-2007 00:07:00 05-APR-2007 00:09:00 A B 00:07 3 05-APR-2007 00:09:00 05-APR-2007 00:10:00 B A 05-APR-2007 00:10:00 05-APR-2007 00:12:00 5 A A 00:10 4 05-APR-2007 00:12:00 05-APR-2007 00:15:00 6 A A 00:12 5 05-APR-2007 00:15:00 05-APR-2007 00:17:00 7 A A 00:15 6 05-APR-2007 00:17:00 05-APR-2007 00:18:00 A B 00:17 7
With the above, you would like to have the pairs:
00:02-00:05 (1a), 00:02-00:07 (1b), 00:05-00:07 (1c)
The next sequence:
00:10-00:12 (2a), 00:10-00:15 (2b), 00:12-00:15 (2c), 00:10-00:17
(2d), 00:12-00:17 (2e), 00:15-00:17 (2f)
...
The output of my last post shows this, which is essentially the same as the above, just in a slightly different order:
05-APR-2007 00:02:00 05-APR-2007 00:05:00 00:02-00:05 (1a) 05-APR-2007 00:05:00 05-APR-2007 00:07:00 00:05-00:07 (1c) 05-APR-2007 00:02:00 05-APR-2007 00:05:00 00:02-00:07 (1b) 05-APR-2007 00:10:00 05-APR-2007 00:12:00 00:10-00:12 (2a) 05-APR-2007 00:12:00 05-APR-2007 00:15:00 00:12-00:15 (2c) 05-APR-2007 00:10:00 05-APR-2007 00:12:00 00:10-00:15 (2b) 05-APR-2007 00:15:00 05-APR-2007 00:17:00 00:15-00:17 (2f) 05-APR-2007 00:12:00 05-APR-2007 00:15:00 00:12-00:17 (2e) 05-APR-2007 00:10:00 05-APR-2007 00:12:00 00:10-00:17 (2d)
The execution plan does not look as bad as I expected - a proper
DBMS_XPLAN (rather than the execution plan I stripped from a 10046
trace):
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- |* 1 | CONNECT BY WITHOUT FILTERING | | 1 | | 1652 | 00:00:00.07 | 23 | 19 | | | | | 2 | VIEW | | 1 | 1035 | 1194 |00:00:00.07 | 23 | 19 | | | | | 3 | WINDOW NOSORT | | 1 | 1035 | 1194 | 00:00:00.07 | 23 | 19 | 73728 | 73728 | | | 4 | VIEW | | 1 | 1035 | 1194 |00:00:00.07 | 23 | 19 | | | | | 5 | WINDOW SORT | | 1 | 1035 | 1194 | 00:00:00.06 | 23 | 19 | 43008 | 43008 |38912 (0)| | 6 | VIEW | | 1 | 1035 | 1194 |00:00:00.06 | 23 | 19 | | | | | 7 | SORT ORDER BY | | 1 | 1035 | 1194 | 00:00:00.06 | 23 | 19 | 64512 | 64512 |57344 (0)| | 8 | VIEW | | 1 | 1035 | 1194 |00:00:00.04 | 23 | 19 | | | | | 9 | UNION-ALL | | 1 | | 1194 |00:00:00.04 | 23 | 19 | | | | |* 10 | HASH JOIN RIGHT OUTER | | 1 | 1000 | 1000 | 00:00:00.04 | 14 | 12 | 1155K| 1155K| 1219K (0)| | 11 | TABLE ACCESS FULL | T2 | 1 | 300 | 300 | 00:00:00.02 | 7 | 6 | | | | | 12 | TABLE ACCESS FULL | T1 | 1 | 1000 | 1000 | 00:00:00.02 | 7 | 6 | | | | | 13 | MERGE JOIN ANTI | | 1 | 35 | 194 | 00:00:00.02 | 9 | 7 | | | | | 14 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 300 | 300 |00:00:00.01 | 2 | 1 | | | | | 15 | INDEX FULL SCAN | T2_IND1 | 1 | 300 | 300 |00:00:00.01 | 1 | 1 | | | | |* 16 | SORT UNIQUE | | 300 | 1000 | 106 | 00:00:00.02 | 7 | 6 | 64512 | 64512 |57344 (0)| | 17 | INDEX FAST FULL SCAN | T1_IND1 | 1 | 1000 | 1000 |00:00:00.02 | 7 | 6 | | | |
Predicate Information (identified by operation id):
1 - access("TS"+1=PRIOR NULL)
10 - access("T1"."V1"="T2"."V1")
16 - access("T1"."V1"="T2"."V1")
filter("T1"."V1"="T2"."V1")
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Fri Apr 06 2007 - 10:49:36 CDT
 |
 |