1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Recursive query

Discussion in 'SQL PL/SQL' started by Stuart Holroyd, Aug 5, 2015.

  1. Stuart Holroyd

    Stuart Holroyd Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Victoria, BC
    Table a is the main table
    Tables b and c are related thru the rsn field in table a.
    Table c has a self referencing column called parent_rsn.

    This query works but is very slow when table c has lots of rows (100,000s).

    select a.rsn,b.col1,b.col2,c.col3
    from a
    join b on a.rsn = b.a_rsn
    join c on a.rsn = c.a_rsn
    where
    a.col4 = 'foo' and
    b.col5 = 'bar' and
    c.col9 = 'bla' and
    c.rsn in
    (
    with r (rsn) as
    (
    select rsn from c where c.col6 = 1234
    union all
    select rs.rsn from c r2,r where r2.parent_rsn = r.rsn
    )
    select * from r
    )
    order by a.rsn;


    The query is slow because the anchor select references table c which has lots of rows that satisfy the where clause and the anchor select looks at them all.

    How can I restrict the anchor clause to only look at related rows. I want the anchor clause to be something like

    select rsn from c where c.a_rsn = a.rsn and c.col6 = 1234

    where a.rsn is the parent row related to a small subset of table c.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Your query is bizarre. Did you really mean to reference the WITH result from within the WITH query and if so... why? I can't test your query, but I have a hard time believing it works -- or at least that it retrieves valid results.

    with r (rsn) as
    (
    select rsn from c where c.col6 = 1234
    union all
    select rs.rsn from c r2, r
    where r2.parent_rsn = r.rsn
    )

    Also, you have 'SELECT rs.rsn..." but the alias 'rs' isn't declared anywhere. This also leads me to believe your example does not, in fact, work. Tuning an invalid query is effectively impossible.
     
  3. Stuart Holroyd

    Stuart Holroyd Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Victoria, BC
    rs is a typo, it should have been r2. Sorry about that. I tried to simplify the query.

    Here is the actual query. This version returns valid result in about 20 seconds, in a quiet development database (Oracle and a Postgresql). There are production server issues beyond my control which makes anything over 20 seconds a problem. We have many canned queries which use the same SELECT fields, FROM tables (with JOIN) and the only thing that changes is the WHERE clause...

    SELECT * FROM (select inner_query.*, rownum rnum FROM (
    SELECT "LOG"."ID" AS "log_id",
    "AUTHOR"."LAST_NAME" AS "ath_last_name",
    "OFFICE"."NAME" AS "off_name",
    (SELECT "STRING_VALUE" FROM "LOG_UDF" WHERE "FIELD"='lud_subject' AND "LOG_UDF"."LOG_RSN"="LOG"."RSN" AND rownum <= 1 ) AS "lud_subject",
    "LOG"."TYPE" AS "log_type",
    "REFERRAL"."ACTION" AS "ref_action",
    "REFERRAL"."STATE" AS "ref_state",
    "REFERRAL"."STATUS" AS "ref_status",
    "REFERRAL"."DATE_DUE" AS "ref_date_due",
    "REFERRAL"."RSN" AS "ref_rsn",
    "REFERRAL"."DATE_COMPLETED" AS "ref_date_completed",
    "LOG"."RSN" AS "log_rsn",
    "LOG"."RECORD_TYPE" AS "log_record_type",
    "LOG"."BATCH" AS "log_batch",
    "AUTHOR"."FIRST_NAME" AS "ath_first_name",
    "REFERRAL"."FROM_OFFICE_RSN" AS "ref_from_office_rsn",
    "REFERRAL"."TO_OFFICE_RSN" AS "ref_to_office_rsn",
    (select count(*) from "ATTACHMENT" where "ATTACHMENT"."REFERRAL_RSN" = "REFERRAL"."RSN") as "X_HAS_ATTACHMENTS",
    (case (select count(*) from "SELECTION" where "SELECTION"."DIV_NAME" = 'src-170' AND "SELECTION"."ACCOUNT_RSN" = 408 AND "SELECTION"."REF_RSN" = "REFERRAL"."RSN") when 0 then '' else 'checked' end) as "X_MARKED"
    FROM "LOG"
    JOIN "REFERRAL" ON "LOG"."RSN" = "REFERRAL"."LOG_RSN"
    JOIN "OFFICE" ON "REFERRAL"."TO_OFFICE_RSN" = "OFFICE"."RSN"
    JOIN "LOG_AUTHOR" ON "LOG"."RSN" = "LOG_AUTHOR"."LOG_RSN"
    JOIN "AUTHOR" ON "LOG_AUTHOR"."AUTHOR_RSN" = "AUTHOR"."RSN"
    WHERE "LOG_AUTHOR"."PRIMARY_AUTHOR" = 1 AND
    "LOG"."DATE_CREATED" >= 1407279403 AND
    "REFERRAL"."DATE_COMPLETED" = 0 AND
    "REFERRAL"."DATE_DUE" > 0 AND
    "REFERRAL"."DATE_DUE" < 1438758000 AND
    "REFERRAL"."STATE" = 'Active' AND
    "REFERRAL"."RSN" in
    (
    WITH "R" ( "RSN" ) as
    (
    SELECT "RSN" FROM "REFERRAL" WHERE "FROM_OFFICE_RSN" = 31
    UNION ALL
    SELECT "R2"."RSN" FROM "REFERRAL" "R2","R" WHERE "R2"."PARENT_RSN" = "R"."RSN"
    ) SELECT * FROM "R"
    )
    ORDER BY "LOG"."ID" ) inner_query WHERE rownum <= 10)
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Even with the full query, I don't understand why your WITH query is recursively referencing itself. I cannot imagine why you would do this, but it looks like something that would be really ugly for the SQL engine to process.

    Code (Text):

    AND   "REFERRAL"."RSN" IN  (WITH "R" ( "RSN" ) as
                                (SELECT "RSN"
                                 FROM   "REFERRAL"
                                 WHERE  "FROM_OFFICE_RSN" = 31
                                 UNION ALL
                                 SELECT "R2"."RSN"
                                 FROM   "REFERRAL" "R2","R"
                                 WHERE "R2"."PARENT_RSN" = "R"."RSN"
                                )
                                SELECT * FROM " R"
                                )
    Since all of the columns being referenced are in the REFERRAL table -- I don't really see the reason for the subquery at all. The subquery looks like it could be replaced by the following logic:

    Code (Text):

    AND   (   "REFERRAL"."FROM_OFFICE_RSN" = 31
           OR "REFERRAL"."PARENT_RSN" = "REFERRAL"."RSN"
          )
    I don't know enough about the data in the REFERRAL table to be sure the above is equivalent, though.