1 2 Previous Next 17 Replies Latest reply: Aug 16, 2010 3: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,

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