This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Aug 16, 2010 1:47 PM by 783956 Go to original post RSS
  • 15. Re: Tabibitosan method tutorial by Aketi Jyuuzou
    user503699 Expert
    Currently Being Moderated
    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
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    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
    783956 Journeyer
    Currently Being Moderated
    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