This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Dec 11, 2012 11:10 PM by Ayham Go to original post RSS
  • 15. Re: SQLPLUS hanging when execute this  Query
    chris227 Guru
    Currently Being Moderated
    Sorry, i dont have any time yet to spent more on this. I dont have had an idea come quickly to my mind last two days.
    As jeneesh pointed out, best approach will be to cut down the possible pathes in the connect bys.
    I still wonder why there are so many. I feel that there must be a logical mistake or incompletness in the query, but i dont see it yet (perhaps frank would).
    Perhaps i find some time (and fun) next weekend for this.
  • 16. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Many thanks for you. You are kind person.
    If you get any other idea please let me know.

    Thanks for all.
  • 17. Re: SQLPLUS hanging when execute this  Query
    BrendanP Journeyer
    Currently Being Moderated
    I've done a bit more analysis on this interesting problem.

    The efficient way to do this seems to be through a sequence of network searches, starting at each iteration from an unassigned node, and at the end of the iteration assigning all nodes encountered to the iteration's network index. This seems, however, to require coordination through PL/SQL rather than a pure SQL solution.

    In the earlier SQL solutions of Chris and myself nocycle tree-walks are employed to do the network searches but, without the PL/SQL co-ordination, needed to start from every node. This is obviously going to be expensive. There is also a second problem: the nocycle options on the tree-walks prevent infinite looping but still permit many visits to the same node, and this causes an explosive growth in the number of records processed. I ran a tree-walk on subsets of the original 150-record data set, starting from '0002' and limiting the records to those referencing a small number of nodes, as follows (and ignoring zero-percentages in the query):

    loc1 or loc2 in: ('0001', '0002', '0021')
    N1         N2      ID N_ROOT LEV I
    ---------- ---------- ------ --- -
    0          0002     0 0002     0 0
    .0002      0001     8 0002     1 0
    ..0001     0009    13 0002     2 0
    ...0009    0002    39 0002     3 1
    ..0001     0010     1 0002     2 0
    ...0010    0002    14 0002     3 1
    ...0010    0020    15 0002     3 0
    ....0020   0001     9 0002     4 1
    ....0020   0013    21 0002     4 0
    .....0013  0001     4 0002     5 1
    .....0013  0002    20 0002     5 1
    ....0020   0015    23 0002     4 0
    .....0015  0002    22 0002     5 1
    ....0020   0018    29 0002     4 0
    .....0018  0001     7 0002     5 1
    .....0018  0002    28 0002     5 1
    etc.
    672 rows selected.
    
    Elapsed: 00:00:05.81
    loc1 or loc2 in: ('0001', '0002', '0021', '0004')
    N1           N2       ID N_ROOT  LEV I
    ------------ ----------- ------- --- -
    0            0002      0 0002      0 0
    .0002        0001      8 0002      1 0
    ..0001       0009     13 0002      2 0
    ...0009      0002     48 0002      3 1
    ..0001       0010      1 0002      2 0
    ...0010      0002     14 0002      3 1
    ...0010      0004     16 0002      3 0
    ....0004     0013     25 0002      4 0
    .....0013    0001      4 0002      5 1
    .....0013    0002     23 0002      5 1
    .....0013    0020     24 0002      5 0
    ......0020   0001      9 0002      6 1
    ......0020   0010     15 0002      6 1
    ......0020   0015     28 0002      6 0
    .......0015  0002     27 0002      7 1
    .......0015  0004     29 0002      7 1
    ......0020   0018     37 0002      6 0
    .......0018  0001      7 0002      7 1
    .......0018  0002     36 0002      7 1
    .......0018  0004     38 0002      7 1
    etc.
    3879 rows selected.
    
    Elapsed: 00:00:33.01
    The tree-walk code was:
    WITH nod AS (
    SELECT DISTINCT loc1 n
      FROM temp_value
    ), rsf (n1, n2, id, n_root, lev) AS (
    SELECT '0' n1, n n2, 0 id, n n_root, 0 lev
      FROM nod WHERE n = '0002'
     UNION ALL
    SELECT CASE WHEN l.loc1 = r.n2 THEN l.loc1 ELSE l.loc2 END, 
           CASE WHEN l.loc2 = r.n2 THEN l.loc1 ELSE l.loc2 END, 
           l.id, r.n_root, lev + 1
      FROM rsf r
      JOIN temp_value l
        ON (l.loc1 = r.n2 OR l.loc2 = r.n2)
       AND l.id != r.id
       AND l.percentage > 0
    ) SEARCH DEPTH FIRST BY n2 SET A
    CYCLE n2 SET is_cycle TO '1' DEFAULT '0'
    SELECT LPad ('.', lev, '.') || n1 n1, n2, id, n_root, lev, is_cycle
      FROM rsf
     ORDER BY A
    A couple of years ago I wrote my own general network searching package in PL/SQL (it's on Scribd), and when I plugged in the logic for this case it returned the network structure for the full data set in about 0.01 seconds, again starting from '0002', as follows (correction, this is a restricted one):
    Node Tree Listing (* means repeated node) ...
    Node                                                       Link Info
    0002
    f 0001                                                     8
    .t 0009*                                                   13
    .t 0010*                                                   1
    .t 0011*                                                   2
    .t 0012*                                                   3
    .t 0013*                                                   4
    .t 0014                                                    5
    .t 0017*                                                   6
    .t 0018*                                                   7
    .t 0020                                                    9
    t 0007                                                     37
    t 0008                                                     38
    t 0009                                                     39
    f 0010                                                     14
    .t 0020*                                                   15
    f 0011                                                     16
    f 0012                                                     18
    f 0013                                                     20
    .t 0020*                                                   21
    f 0015                                                     22
    .t 0020*                                                   23
    f 0017                                                     26
    f 0018                                                     28
    .t 0020*                                                   29
    In fact, there is only one network in the test example, so only one iteration would be needed in this case. The nocycle logic that I employed prevents expansion of a node if it has previously been encountered anywhere, whereas Oracle's logic is different. It looks, I think, only in the ancestry for duplicates and thus permits a larger search.

    I suppose the upshot of this is that an efficient solution is possible in PL/SQL along the lines indicated, but it seems to me that Oracle's SQL tree-walking features may not be suitable here. Let me know of any other ideas.

    Edited by: BrendanP on 04-Dec-2012 10:18
    Network listing above is for restricted set, full listing as 72 links and takes .03s.
  • 18. Re: SQLPLUS hanging when execute this  Query
    jihuyao Journeyer
    Currently Being Moderated
    Here is one by pl/sql to determine the groups. Probably sql with model clause can take care the double infinite loops.

    Need further clarification of the business logic for the last 3 records with id in 6,7,8
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2  i   number :=0 ;
      3  min_loc  varchar2(50) ;
      4  v_sql  long ;
      5  begin
      6  v_sql := 'update temp set gid=null' ;
      7  execute immediate v_sql ;
      8  loop
      9   select min(loc) into min_loc
     10   from (
     11   select loc1 loc from temp where gid is null and percentage>0
     12   union all
     13   select loc2 loc from temp where gid is null and percentage>0
     14   ) ;
     15   i:=i+1;
     16   update temp set gid=i
     17   where (loc1=min_loc or loc2=min_loc) and percentage>0 ;
     18   exit when sql%rowcount=0 ;
     19  loop
     20   update temp set gid=i
     21   where (
     22   loc1 in (select loc1 from temp where gid=i union all select loc2 from temp where gid=i) or
     23   loc2 in (select loc1 from temp where gid=i union all select loc2 from temp where gid=i)
     24   ) and gid is null and percentage>0 ;
     25   exit when sql%rowcount=0 ;
     26  end loop ;
     27  end loop ;
     28  commit ;
     29* end ;
    SQL> /
    
    PL/SQL procedure successfully completed.
    
      1* select * from temp
    SQL> /
    
            ID LOC1                 LOC2                 PERCENTAGE        GID
    ---------- -------------------- -------------------- ---------- ----------
             1 1                    2                            20          1
             2 1                    3                            40          1
             3 8                    6                            25          2
             4 6                    10                           20          2
             5 11                   10                           10          2
             6 15                   14                            0
             7 10                   14                           45          2
             8 10                   15                           35          2
    
    8 rows selected.
  • 19. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Really I appreciate your clarifications BrendanP , I am maybe two months in this issue. From two days before I feel really frustrated. Anyway, I see the new ideas from others and I will try the given PL/SQL Code from jihuyao . And waiting also Chairs777 this weekend.

    many thanks for you.
    Ayham.
  • 20. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Hi jihuyao ,
    Many thanks i will test it and back to you. i am so glad to see your code and results.
    regards
  • 21. Re: SQLPLUS hanging when execute this  Query
    BrendanP Journeyer
    Currently Being Moderated
    I realised this morning that the functionality to list all networks would make a nice addition to my own network search package, which previously required the input of a root node. I have therefore extended it to allow for input of a list of nodes, and now it lists all the distinct networks, with isolated nodes grouped together. I applied it to this locations problem initially, and then to the problem of identifying foreign key networks in a database, which has already proved useful at work. For the locations problem, I added two isolated nodes to Aywan's data set, then duplicated the set with new ids and node numbers. This gives 4 isolated nodes, plus two distinct networks of 21 nodes and 72 links. The output returned in 0.2 seconds. I'll give the first few lines of output in each section below.
    Node Tree Listing for root 0001 (72 links and 21 nodes, * means repeated node) ...
    ==================================================================================
    Node               Link Info
    0001            
     t 0002            8
      t 0007           127
      t 0008           128
       f 0003*         141
       f 0016*         102
       f 0021*         138
    etc.
    
    Node Listing...
    ===============
    Node Id            Node Info           Lev Prior Node       Links  Leaf
    0001               X                     0 (no prior node)     10
    0002               X                     1 0001                11
    0003               X                     2 0009                 5  *
    0004               X                     2 0010                 5  *
    0005               X                     2 0010                 3  *
    etc.
    
    Node Tree Listing for root 1101 (72 links and 21 nodes, * means repeated node) ...
    ==================================================================================
    Node               Link Info
    1101
     t 1102            208
      t 1107           327
      t 1108           328
       f 1103*         341
       f 1116*         302
       f 1121*         338
    etc.
    
    Node Listing...
    ===============
    Node Id            Node Info           Lev Prior Node       Links  Leaf
    1101               X                     0 (no prior node)     10
    1102               X                     1 1101                11
    1103               X                     2 1109                 5  *
    1104               X                     2 1110                 5  *
    1105               X                     2 1110                 3  *
    etc.
    
    No linked nodes found for root 1198
    ===================================
    
    Node Listing...
    ===============
    Node Id           Node Info            Lev Prior Node       Links  Leaf
    1198              X                      0 (no prior node)      0  *
    
    No linked nodes found for root 1199
    ===================================
    etc.
    
    Networks identified...
    ======================
    
    Network 0 (i.e. all isolated nodes)
    ===================================
      1198
      1199
      3398
      3399
    *** 4 nodes in network 0
    
    Network 1
    =========
      0001
      0002
      0003
      0004
      0005
    etc.
    *** 21 nodes in network 1
    
    Network 2
    =========
      1101
      1102
      1103
      1104
      1105
    etc.
    *** 21 nodes in network 2
    
    Timer Set: Network, Constructed at 05 Dec 2012 11:43:40, written at 11:43:41
    ============================================================================
    [Timer timed: Elapsed (per call): 0.05 (0.000051), CPU (per call): 0.05 (0.000050), 
     calls: 1000, '***' denotes corrected line below]
    
    Timer              Elapsed          CPU          Calls        Ela/Call        CPU/Call
    -------------   ----------   ----------   ------------   -------------   -------------
    Node info             0.00         0.00             46         0.00006         0.00000
    Open node             0.02         0.02             46         0.00045         0.00043
    Fetch                 0.03         0.01            294         0.00010         0.00003
    Write tree            0.08         0.06              2         0.03829         0.03000
    Write nodes           0.04         0.03              6         0.00631         0.00500
    List networks         0.02         0.02              1         0.02084         0.02000
    (Other)               0.03         0.03              1         0.02533         0.03000
    -------------   ----------   ----------   ------------   -------------   -------------
    Total                 0.21         0.17            396         0.00054         0.00043
    -------------   ----------   ----------   ------------   -------------   -------------
    My solution is of course more general than you need, but this may give you a benchmark at least. I'll publish the code in due course.
  • 22. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Hi jihuyao,

    Many thanks for you and the PL/Code give me the correct answer. But i have also on more condition on these results.

    the condition is

    1- The Correlation between two numbers as show in table for id no 1 where Loc1 value is 1 and loc2 value is 2 and the percentage is 90
    2- one of these numbers is 2 has another Correlation in id 5 has Correlation with other number (this number 10) with percentage 20
    3- actually, these number (1,2,10)in one group that is correct. but the coming condition break this rule.
    4- if the another number is 10 has correlation with another number as id 4 (10,8) and has higher percentage is 100 compare to 2,10 the percentage is 20 and no direct Correlation between 1 and 10_ . so, ignore the relation of (2,10) that in id5 becuase it has percentage less than id 4(10,8).


    That mean 1,2 in group 1
    And 8,10 in group 2
    This is sample data
     
    Create table  table test (id number(9),loc1 number(9), loc2 number(9), per number(9), gid number(9)) 
    Insert  values into test (1 ,1,2,90,null); 
    Insert  values into test (2 ,5,7,60,null); 
    Insert  values into test (3 ,4,3,30,null); 
    Insert  values into test (4 ,10,8,100,null); 
    Insert  values into test (5 ,2,8,20,null); 
    Insert  values into test (6 ,8,4,50,null); 
    Insert  values into test (7 ,7,12,100,null); 
    Expectd reuslts
     
    Id                  Doc1             Doc2             Per                gid 
    1                     1                  2                  90                1 
    2                     5                  7                  60                2 
    7                     7                12                 100                2 
    3                     4                 3                  70                 3 
    4                   10                 8                  100                4 
    6                     8                 4                   50                 4 
    In general talking. How can we group two numbers togather with less percentage of Correlation where these two numbers can be belong to another group with high Correlation percentage.


    Regards

    Edited by: Ayham on Dec 5, 2012 9:52 PM

    Edited by: Ayham on Dec 5, 2012 11:12 PM
  • 23. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Many thanks BrendanP .

    I got your point.
  • 24. Re: SQLPLUS hanging when execute this  Query
    jihuyao Journeyer
    Currently Being Moderated
    Let me think about it and get back to you.
  • 25. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Ok. Many thanks in advance.
    regards
  • 26. Re: SQLPLUS hanging when execute this  Query
    jihuyao Journeyer
    Currently Being Moderated
    Let us correct some errors first, based on given data and description
    Create table  table test (id number(9),loc1 number(9), loc2 number(9), per number(9), gid number(9)) 
    Insert  values into test (1 ,1,2,90,null); 
    Insert  values into test (2 ,5,7,60,null); 
    Insert  values into test (3 ,4,3,30,null); 
    Insert  values into test (4 ,10,8,100,null); 
    Insert  values into test (5 ,2,8,20,null);         --for id=5 loc2=10 (not 8)
    Insert  values into test (6 ,8,4,50,null); 
    Insert  values into test (7 ,7,12,100,null);
    Id                  Doc1             Doc2             Per                gid 
    1                     1                  2                  90                1 
    2                     5                  7                  60                2 
    7                     7                12                 100                2 
    3                     4                 3                  70                 3       --for id=3 percentage=30 (not 70)
    4                   10                 8                  100                4 
    6                     8                 4                   50                 4
    Now let us run the pl/sql using view temp instead of base table temp_base, and check result
    SQL> rename temp to temp_base ;
    
    Table renamed.
    
    SQL> create or replace view temp as
      2  select * from temp_base ;
    
    --run pl/sql
    --then check result
    SQL> select * from temp ;
    
            ID LOC1                 LOC2                 PERCENTAGE        GID
    ---------- -------------------- -------------------- ---------- ----------
             1 1                    2                            90          1
             2 5                    7                            60          2
             3 4                    3                            30          1
             4 10                   8                           100          1
             5 2                    10                           20          1
             6 8                    4                            50          1
             7 7                    12                          100          2
    Now applying the new rule, based on my best understanding, any weak link (where percentage<50) is a broken link, that is,
    percentage=0, so let us re-create the view temp, run pl/sql and check result again
    create or replace view temp as
    select id, loc1, loc2,
    case when percentage<50 then 0 else percentage end percentage,
    gid
    from temp_base
    /
    
    --run pl/sql
    --then check result
            ID LOC1                 LOC2                 PERCENTAGE        GID
    ---------- -------------------- -------------------- ---------- ----------
             1 1                    2                            90          1
             2 5                    7                            60          3
             3 4                    3                             0
             4 10                   8                           100          2
             5 2                    10                            0
             6 8                    4                            50          2
             7 7                    12                          100          3
    Looking at the result output, it is one step away from your expected result, that is,

    1. do not display any broken link if both loc1 and loc2 belong to any groups (for id=5)
    2. otherwise display the broken link as a new group (for id=3)

    If that is true, query the temp view with above conditions for the expected result.
  • 27. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Yes. id=3is 30 not 70, You are right. but for id=5 loc2=8 not 10.

    The weak link break only in case of one of these numbers of this link has link with strong link (that mean has higher percentage with other links)
    with another number.

    in our example
    the weak link is id=5 becuase has two numbers(8,2) with values is 20 where one of these two numbers is 8 that has(belong to) strong link with id=4 (10,8).
    So, break the weak link that is (id=5(8,2)) to strong link that is(id=4(8,10)) that mean 2,8,10 with be in one group.

    The break weak link only when one of numbers of weak link has link with another strong link(that has high percentage)._

    So, break weak link and put it with the strong link(that has high percentage) group not in new group.

    Edited by: Ayham on Dec 6, 2012 11:14 PM
  • 28. Re: SQLPLUS hanging when execute this  Query
    jihuyao Journeyer
    Currently Being Moderated
    in our example
    the weak link is id=5 becuase has two numbers(8,2) with values is 20 where one of these two numbers is 8 that has(belong to) strong link with id=4 (10,8).
    So, break the weak link that is (id=5(8,2)) to strong link that is(id=4(8,10)) that mean 2,8,10 with be in one group.
    ...that mean 2,8,10 with be in one group, is it contradictory to your expected result with 4,8,10 in one group?
  • 29. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    sorry , you are right
    8,4,10 in one group.

    Expected reuslts.
    Id                  Doc1             Doc2               Per               gid 
    1                     1               2                  90               1 
    2                     5               7                  60               2 
    7                     7               12                 100              2 
    3                     4               3                  30               3       
    4                    10                8                  100             3 
    6                     8               4                  50               3
    2,1 in group one
    5,7,12 in group two
    4,8,10 in group three

    Edited by: Ayham on Dec 7, 2012 8:22 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points