Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    9

    Unanswered: recursive subquery factoring: how it works?

    Have table
    Code:
    ALTER TABLE ICE.PRT
     DROP PRIMARY KEY CASCADE;
    
    DROP TABLE ICE.PRT CASCADE CONSTRAINTS;
    
    CREATE TABLE ICE.PRT
    (
      ID         INTEGER,
      PARENT_ID  INTEGER,
      VAL        INTEGER
    )
    TABLESPACE USERS
    RESULT_CACHE (MODE DEFAULT)
    PCTUSED    0
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    LOGGING 
    NOCOMPRESS 
    NOCACHE
    NOPARALLEL
    MONITORING;
    
    
    CREATE INDEX ICE.IDX_PRT_PARENT_ID ON ICE.PRT
    (PARENT_ID)
    LOGGING
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    NOPARALLEL;
    
    
    --  There is no statement for index ICE.SYS_C0011935.
    --  The object is created when the parent object is created.
    
    ALTER TABLE ICE.PRT ADD (
      PRIMARY KEY
      (ID)
      USING INDEX
        TABLESPACE USERS
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    (
                    INITIAL          64K
                    NEXT             1M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                    FLASH_CACHE      DEFAULT
                    CELL_FLASH_CACHE DEFAULT
                   )
      ENABLE VALIDATE);
    
    ALTER TABLE ICE.PRT ADD (
      CONSTRAINT FK_PRT_PARENT_ID 
      FOREIGN KEY (PARENT_ID) 
      REFERENCES ICE.PRT (ID)
      ENABLE VALIDATE);
    and data inside it:
    Code:
    truncate table prt
     
    insert into prt
    (id)
    select level-1 from dual
    connect by level<10000
    
    update prt set
    parent_id = trunc(id/10)
    
    update prt set
    val=1
    where not exists (select 1 from prt a where A.PARENT_ID=prt.id)
    Now lets rollup from bottom to top:

    Code:
    with cte (id, PARENT_ID, VAL, lvl) as
    (
    select A.ID, A.PARENT_ID, A.VAL, 1 as lvl
    from prt a
    where not exists (select 1 from prt b where B.PARENT_ID=A.ID)
    union all
    select c.ID, c.PARENT_ID, 0, cte.lvl+1 as lvl --SUM(cte.VAL) as val
    from prt c, cte
    where cte.PARENT_ID=c.ID
    --group by A.ID, A.PARENT_ID 
    )
    select * from cte
    order by cte.id
    Why did i obtain that:
    [Error] Execution (4: 6): ORA-32044: cycle detected while executing recursive WITH query
    ?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is parent_id of id=0?
    Isn't it 0?
    If so, id=0 may cycle to itself.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by icegood View Post
    Have table
    Code:
    -- - -   E t c  . . .
    WITH Cte ( Id, Parent_Id, Val, Lvl )
         AS (SELECT A.Id, A.Parent_Id, A.Val 1 AS Lvl
               FROM Prt A
              WHERE NOT EXISTS
                       (SELECT 1
                          FROM Prt B
                         WHERE B.Parent_Id = A.Id)
             UNION ALL
             SELECT C.Id, C.Parent_Id, 0, Cte.Lvl + 1 AS Lvl --SUM(cte.VAL) as val
               FROM Prt C, Cte
              WHERE Cte.Parent_Id = C.Id--group by A.ID, A.PARENT_ID
            )
      SELECT *
        FROM Cte
    ORDER BY Cte.Id
    Why did i obtain that:

    ?
    After the UNION ALL you have the cycle when you SELECT ... FROM Prt C, Cte
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2014
    Posts
    9
    Quote Originally Posted by tonkuma View Post
    What is parent_id of id=0?
    Isn't it 0?
    If so, id=0 may cycle to itself.
    Yep, thats was point

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •