1 2 Previous Next 17 Replies Latest reply on Aug 16, 2010 8:47 PM by 783956 Go to original post
• ###### 15. Re: Tabibitosan method tutorial by Aketi Jyuuzou
Aketi Jyuuzou wrote:
About 2 years before,I solved it :D
range query
Aketi,

Many thanks for your response.
Is this the only way (using SQL) to solve this problem?
The main reason for asking for alternative solution is this solution requires accessing and sorting the table twice.
``````SQL> select * from table(dbms_xplan.display_cursor) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9skznz4u401t1, child number 0
-------------------------------------
select id,x,y from (select id,x,y,min(x) over (partition by id) startx,
min(x) over (partition by id order by x range between interval '1' minute
following and unbounded following) nextval from t1) start with x = startx
connect by prior nextval = x

Plan hash value: 775368986

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |     4 (100)|          |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|*  2 |   VIEW                    |      |    16 |  1136 |     4  (25)| 00:00:01 |
|   3 |    WINDOW SORT            |      |    16 |   240 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL     | T1   |    16 |   240 |     3   (0)| 00:00:01 |
|*  5 |   HASH JOIN               |      |       |       |            |          |
|   6 |    CONNECT BY PUMP        |      |       |       |            |          |
|   7 |    VIEW                   |      |    16 |   704 |     4  (25)| 00:00:01 |
|   8 |     WINDOW SORT           |      |    16 |   240 |     4  (25)| 00:00:01 |
|   9 |      TABLE ACCESS FULL    | T1   |    16 |   240 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("X"=PRIOR NULL)
2 - filter("X"="STARTX")
5 - access("X"=PRIOR NULL)``````
p.s. Please don't suggest MODEL clause ;)

Edited by: user503699 on Aug 2, 2010 6:52 PM
• ###### 16. Re: Tabibitosan method tutorial by Aketi Jyuuzou
user503699 wrote:
Is this the only way (using SQL) to solve this problem?
The main reason for asking for alternative solution is this solution requires accessing and sorting the table twice.
Ummm

I think that there are 2 solutions.
One is recursive with clause insted of above HierarchicalQuery.
And The other is PipeLineTableFunction.
• ###### 17. Re: Tabibitosan method tutorial by Aketi Jyuuzou
Hi Alan,

>
I am a little dense,...
>

No, you're not... I had to think about it too ;)

Indeed, it is very slick. A known monotonous sequence (the row numbers) applied to a sparse sequence of monotonous values (the other sequence) reveals the sparse groups in the sequence because their difference is constant in each group.

Very nice and elegant! :)

Thank you Aketi !!

John.
1 2 Previous Next