Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Question Unanswered: Problem with connect by

    hi all,

    im working on this sql statement,

    select distinct bunit.*,ttbc.txt_tbc_val buidtyp_desc
    from bunit left join ttbc on bunit.bunittyp_id = ttbc.txt_tbc_cde , mbr a
    where bunit.BUNIT_NUM in (964, 5780, 7588, 5729, 5800) AND (a.mbr_bunit_num=bunit.bunit_num )
    or bunit.bunit_dt_start is null)
    connect by a.ctorg_bunit_num = PRIOR a.mbr_bunit_num start with a.ctorg_bunit_num = 8223


    this query works fine when there are many BUNIT_NUM are present within the in clause. But when there is only one BUNIT_NUM within the in clause the query seems to take longer time to execute.
    why this is hapening.
    is there a confilct with single "in" statement and connect by clause.

    thanks.
    rush

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with connect by

    What does EXPLAIN PLAN show for the different queries?

  3. #3
    Join Date
    Feb 2004
    Posts
    2

    Re: Problem with connect by

    Hi andrewst,

    I have attached my original queries and their explain plans here.

    Query 1(which is for multiple items in "IN")

    select distinct bunit.*,ttbc.txt_tbc_val buidtyp_desc
    from bunit left join ttbc on bunit.bunittyp_id = ttbc.txt_tbc_cde , mbr a
    where bunit.BUNIT_NUM in (964, 5780, 7588, 5729) AND (a.mbr_bunit_num=bunit.bunit_num )
    AND (a.mbr_dt_end > to_date('2004-02-05', 'YYYY-MM-DD') OR a.mbr_dt_end is null)
    AND (a.mbr_dt_start < to_date('2004-02-26', 'YYYY-MM-DD') OR a.mbr_dt_start is null)
    AND a.status_num in (13) AND (bunit.bunit_dt_end > to_date('2004-02-05', 'YYYY-MM-DD')
    or bunit.bunit_dt_end is null) AND (bunit.bunit_dt_start < to_date('2004-02-26', 'YYYY-MM-DD')
    or bunit.bunit_dt_start is null)
    connect by a.ctorg_bunit_num = PRIOR a.mbr_bunit_num start with a.ctorg_bunit_num = 8223


    Explain Plan -

    Statement Id=8 Type=HASH JOIN
    Cost=105 TimeStamp=11-02-04::18::18:31

    (1) SELECT STATEMENT CHOOSE
    Est. Rows: 11,159 Cost: 864
    (19) SORT UNIQUE
    Est. Rows: 11,159 Cost: 864
    (18) FILTER
    (17) CONNECT BY WITH FILTERING
    (8) FILTER
    (7) COUNT
    (6) HASH JOIN
    Est. Rows: 11,159 Cost: 152
    (2) TABLE ACCESS FULL CARSRM.MBR [Analyzed]
    (2) Blocks: 105 Est. Rows: 9,334 of 9,334 Cost: 12
    Tablespace: TSCARSRM
    (5) HASH JOIN OUTER
    Est. Rows: 10,725 Cost: 105
    (3) TABLE ACCESS FULL CARSRM.BUNIT [Analyzed]
    (3) Blocks: 232 Est. Rows: 8,971 of 8,971 Cost: 24
    Tablespace: TSCARSRM
    (4) TABLE ACCESS FULL CARSRM.TTBC [Analyzed]
    (4) Blocks: 12 Est. Rows: 1,700 of 1,700 Cost: 3
    Tablespace: TSCARSRM
    (16) HASH JOIN
    (9) CONNECT BY PUMP
    (15) COUNT
    (14) HASH JOIN
    Est. Rows: 11,159 Cost: 152
    (10) TABLE ACCESS FULL CARSRM.MBR [Analyzed]
    (10) Blocks: 105 Est. Rows: 9,334 of 9,334 Cost: 12
    Tablespace: TSCARSRM
    (13) HASH JOIN OUTER
    Est. Rows: 10,725 Cost: 105
    (11) TABLE ACCESS FULL CARSRM.BUNIT [Analyzed]
    (11) Blocks: 232 Est. Rows: 8,971 of 8,971 Cost: 24
    Tablespace: TSCARSRM
    (12) TABLE ACCESS FULL CARSRM.TTBC [Analyzed]
    (12) Blocks: 12 Est. Rows: 1,700 of 1,700 Cost: 3
    Tablespace: TSCARSRM


    the second query which takes time

    select distinct bunit.*,ttbc.txt_tbc_val buidtyp_desc
    from bunit left join ttbc on bunit.bunittyp_id = ttbc.txt_tbc_cde , mbr a
    where bunit.BUNIT_NUM in (964) AND (a.mbr_bunit_num=bunit.bunit_num )
    AND (a.mbr_dt_end > to_date('2004-02-05', 'YYYY-MM-DD') OR a.mbr_dt_end is null)
    AND (a.mbr_dt_start < to_date('2004-02-26', 'YYYY-MM-DD') OR a.mbr_dt_start is null)
    AND a.status_num in (13) AND (bunit.bunit_dt_end > to_date('2004-02-05', 'YYYY-MM-DD')
    or bunit.bunit_dt_end is null) AND (bunit.bunit_dt_start < to_date('2004-02-26', 'YYYY-MM-DD')
    or bunit.bunit_dt_start is null)
    connect by a.ctorg_bunit_num = PRIOR a.mbr_bunit_num start with a.ctorg_bunit_num = 8223


    explain plan for the query

    Statement Id=8 Type=TABLE ACCESS
    Cost=24 TimeStamp=11-02-04::18::19:43

    (1) SELECT STATEMENT CHOOSE
    Est. Rows: 100,105,509 Cost: 37,570,337
    (21) SORT UNIQUE
    Est. Rows: 100,105,509 Cost: 37,570,337
    (20) FILTER
    (19) CONNECT BY WITH FILTERING
    (9) FILTER
    (8) COUNT
    (7) MERGE JOIN CARTESIAN
    Est. Rows: 100,105,509 Cost: 128,805
    (4) HASH JOIN OUTER
    Est. Rows: 10,725 Cost: 105
    (2) TABLE ACCESS FULL CARSRM.BUNIT [Analyzed]
    (2) Blocks: 232 Est. Rows: 8,971 of 8,971 Cost: 24
    Tablespace: TSCARSRM
    (3) TABLE ACCESS FULL CARSRM.TTBC [Analyzed]
    (3) Blocks: 12 Est. Rows: 1,700 of 1,700 Cost: 3
    Tablespace: TSCARSRM
    (6) BUFFER SORT
    Est. Rows: 9,334 Cost: 128,802
    (5) TABLE ACCESS FULL CARSRM.MBR [Analyzed]
    (5) Blocks: 105 Est. Rows: 9,334 of 9,334 Cost: 12
    Tablespace: TSCARSRM
    (18) HASH JOIN
    (10) CONNECT BY PUMP
    (17) COUNT
    (16) MERGE JOIN CARTESIAN
    Est. Rows: 100,105,509 Cost: 128,805
    (13) HASH JOIN OUTER
    Est. Rows: 10,725 Cost: 105
    (11) TABLE ACCESS FULL CARSRM.BUNIT [Analyzed]
    (11) Blocks: 232 Est. Rows: 8,971 of 8,971 Cost: 24
    Tablespace: TSCARSRM
    (12) TABLE ACCESS FULL CARSRM.TTBC [Analyzed]
    (12) Blocks: 12 Est. Rows: 1,700 of 1,700 Cost: 3
    Tablespace: TSCARSRM
    (15) BUFFER SORT
    Est. Rows: 9,334 Cost: 128,802
    (14) TABLE ACCESS FULL CARSRM.MBR [Analyzed]
    (14) Blocks: 105 Est. Rows: 9,334 of 9,334 Cost: 12
    Tablespace: TSCARSRM


    thank you for the help
    rush.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with connect by

    Those are very different plans. Obviously, Oracle believes that for the single-item IN list, the different plan is better - apparently wrongly!

    What I do know is that IN lists are transformed like this:

    (col IN (1,2,3)) => (col=1 OR col=2 OR col=3)

    Obviously, (col IN (1)) therefore becomes (col=1), without any ORs. This apparently triggers the different approach, presumably because Oracle thinks the number of rows matching the condition will be smaller.

    Also, if there are histogram statistics on the column then Oracle may consider that the specific value (964) matches a much smaller set of rows than the list (964, 5780, 7588, 5729). Presumably your table stats are up to date?

    I wonder what would happen if you changed the query to use bind variables like:

    where bunit.BUNIT_NUM in (:v1,:v2)

    Otherwise, I guess it may be a case where addition of hints may be required to try to persuade Oracle to use the prefered plan.

Posting Permissions

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