Results 1 to 7 of 7

Thread: Union All!!!

  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Union All!!!

    Im trying to join these 2 queries using UNION ALL. 2 queries take only 30 seconds if run separately.

    But if i try to use UNION ALL its taking forever.

    select tpt_desc, hsv_desc, sum(cnt1), sum(cnt2)
    from
    (
    (
    select a4.tpt_desc tpt_desc,
    a7.hsv_desc hsv_desc,
    count(*) cnt1,
    0 cnt2
    from wmds.tau_treatment_authorization a1,
    wmds.mpg_mem_plan_group a2,
    wmds.tpa_third_party_admin_client a3,
    wmds.tpt_third_party_client_type a4,
    wmds.usr_user a5,
    wmds.stf_staff a6,
    wmds.hsv_hospital_service a7
    where a1.tau_status_summary <> 'CXL'
    and a1.tau_mpg_uid = a2.mpg_uid (+)
    and a2.mpg_tpa_uid = a3.tpa_uid (+)
    and a3.tpa_tpt_code = a4.tpt_code (+)
    and a1.tau_usr_uid_updated_by = a5.usr_uid (+)
    and a5.usr_stf_uid = a6.stf_uid (+)
    and a6.stf_hsv_code = a7.hsv_code (+)
    group by a4.tpt_desc,
    a7.hsv_desc
    )
    UNION ALL
    (
    select b4.tpt_desc tpt_desc,
    b7.hsv_desc hsv_desc,
    0 cnt1,
    count(*) cnt2
    from wmds.tau_treatment_authorization b1,
    wmds.mpg_mem_plan_group b2,
    wmds.tpa_third_party_admin_client b3,
    wmds.tpt_third_party_client_type b4,
    wmds.usr_user b5,
    wmds.stf_staff b6,
    wmds.hsv_hospital_service b7
    where b1.tau_status_summary <> 'CXL'
    and b1.tau_mpg_uid = b2.mpg_uid (+)
    and b2.mpg_tpa_uid = b3.tpa_uid (+)
    and b3.tpa_tpt_code = b4.tpt_code (+)
    and b1.tau_usr_uid_updated_by = b5.usr_uid (+)
    and b5.usr_stf_uid = b6.stf_uid (+)
    and b6.stf_hsv_code = b7.hsv_code (+)
    and exists(
    select b8.mno_tau_uid
    from wmds.mno_mem_note b8
    where b8.mno_tau_uid = b1.tau_uid
    and b8.mno_not_code = 'NOT042'
    and b8.mno_criteria_met_yn = 'Y' )
    group by b4.tpt_desc,
    b7.hsv_desc
    )
    )
    group by tpt_desc, hsv_desc

    ..

    Note ..I ran same query using outer joins.
    from
    (X),
    (Y)
    where X.. = Y. (+)

    But I want to use UNION ALL, as I may have 2 queries usg 2 different sets of table.

    Any help will be appreciated.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    any specific reason for doing union all than union?
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    have you tried the select with union all and without the last group by ?

    Tarry : union all just adds the results of query 2 to the resultset of query 1 without checking if the rows returned were already in the resultset. Therefor it is much faster, especially when retrieving large amounts of data.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Oct 2003
    Posts
    3
    No thats not working either.. I tried infact removing group by from inner two queries too..

    It shows results from 1st query..but not from 2nd.

    I guess something is wrong with EXISTS..when used with UNION/UNION ALL... not sure what!!!

    Any suggestions?



    Originally posted by evanhattem
    hi,

    have you tried the select with union all and without the last group by ?

    Tarry : union all just adds the results of query 2 to the resultset of query 1 without checking if the rows returned were already in the resultset. Therefor it is much faster, especially when retrieving large amounts of data.

    Hope this helps

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    does the following statement work :

    select a4.tpt_desc tpt_desc,
    a7.hsv_desc hsv_desc,
    count(*) cnt1,
    0 cnt2
    from wmds.tau_treatment_authorization a1,
    wmds.mpg_mem_plan_group a2,
    wmds.tpa_third_party_admin_client a3,
    wmds.tpt_third_party_client_type a4,
    wmds.usr_user a5,
    wmds.stf_staff a6,
    wmds.hsv_hospital_service a7
    where a1.tau_status_summary <> 'CXL'
    and a1.tau_mpg_uid = a2.mpg_uid (+)
    and a2.mpg_tpa_uid = a3.tpa_uid (+)
    and a3.tpa_tpt_code = a4.tpt_code (+)
    and a1.tau_usr_uid_updated_by = a5.usr_uid (+)
    and a5.usr_stf_uid = a6.stf_uid (+)
    and a6.stf_hsv_code = a7.hsv_code (+)
    group by a4.tpt_desc,
    a7.hsv_desc
    )
    UNION ALL
    (
    select b4.tpt_desc tpt_desc,
    b7.hsv_desc hsv_desc,
    0 cnt1,
    count(*) cnt2
    from wmds.tau_treatment_authorization b1,
    wmds.mpg_mem_plan_group b2,
    wmds.tpa_third_party_admin_client b3,
    wmds.tpt_third_party_client_type b4,
    wmds.usr_user b5,
    wmds.stf_staff b6,
    wmds.hsv_hospital_service b7
    where b1.tau_status_summary <> 'CXL'
    and b1.tau_mpg_uid = b2.mpg_uid (+)
    and b2.mpg_tpa_uid = b3.tpa_uid (+)
    and b3.tpa_tpt_code = b4.tpt_code (+)
    and b1.tau_usr_uid_updated_by = b5.usr_uid (+)
    and b5.usr_stf_uid = b6.stf_uid (+)
    and b6.stf_hsv_code = b7.hsv_code (+)
    and exists(
    select b8.mno_tau_uid
    from wmds.mno_mem_note b8
    where b8.mno_tau_uid = b1.tau_uid
    and b8.mno_not_code = 'NOT042'
    and b8.mno_criteria_met_yn = 'Y' )
    group by b4.tpt_desc,
    b7.hsv_desc
    )
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Oct 2003
    Posts
    3
    NO..It shows me results from first query (query before UNION ALL...where cnt2 is zero always)...but then it takes forever for 2nd ie query after UNION ALL.




    Originally posted by evanhattem
    does the following statement work :

    select a4.tpt_desc tpt_desc,
    a7.hsv_desc hsv_desc,
    count(*) cnt1,
    0 cnt2
    from wmds.tau_treatment_authorization a1,
    wmds.mpg_mem_plan_group a2,
    wmds.tpa_third_party_admin_client a3,
    wmds.tpt_third_party_client_type a4,
    wmds.usr_user a5,
    wmds.stf_staff a6,
    wmds.hsv_hospital_service a7
    where a1.tau_status_summary <> 'CXL'
    and a1.tau_mpg_uid = a2.mpg_uid (+)
    and a2.mpg_tpa_uid = a3.tpa_uid (+)
    and a3.tpa_tpt_code = a4.tpt_code (+)
    and a1.tau_usr_uid_updated_by = a5.usr_uid (+)
    and a5.usr_stf_uid = a6.stf_uid (+)
    and a6.stf_hsv_code = a7.hsv_code (+)
    group by a4.tpt_desc,
    a7.hsv_desc
    )
    UNION ALL
    (
    select b4.tpt_desc tpt_desc,
    b7.hsv_desc hsv_desc,
    0 cnt1,
    count(*) cnt2
    from wmds.tau_treatment_authorization b1,
    wmds.mpg_mem_plan_group b2,
    wmds.tpa_third_party_admin_client b3,
    wmds.tpt_third_party_client_type b4,
    wmds.usr_user b5,
    wmds.stf_staff b6,
    wmds.hsv_hospital_service b7
    where b1.tau_status_summary <> 'CXL'
    and b1.tau_mpg_uid = b2.mpg_uid (+)
    and b2.mpg_tpa_uid = b3.tpa_uid (+)
    and b3.tpa_tpt_code = b4.tpt_code (+)
    and b1.tau_usr_uid_updated_by = b5.usr_uid (+)
    and b5.usr_stf_uid = b6.stf_uid (+)
    and b6.stf_hsv_code = b7.hsv_code (+)
    and exists(
    select b8.mno_tau_uid
    from wmds.mno_mem_note b8
    where b8.mno_tau_uid = b1.tau_uid
    and b8.mno_not_code = 'NOT042'
    and b8.mno_criteria_met_yn = 'Y' )
    group by b4.tpt_desc,
    b7.hsv_desc
    )

  7. #7
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    then I don't understand. You said running the 2 queries separatly worked fine and ast. Then running the two queries with only the UNION ALL between them does not work.
    Makes me suspicious while the UNION ALL does nothing more then runthe two queries and return their resultsets as one. The only thing might be that it needs more storing while the resultset of the combined queries is larger than the resultsets of the single queries.
    Check to see if there's not enough allocated space to store that large resultset (i guess the machine would do some swapping ?!?).
    Try changing the sort area size and the sort area retained size to a large number.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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