DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Union Slow

  1. #1
    Join Date
    Jul 2005
    Posts
    23

    Union Slow

    I have 2 separate queries that when run separately run in 2 seconds and 0.05 seconds resoectively. When I use UNION, the query takes forever and I have to terminate it without returniung any rows. Can anyone help me out with this ?

    heres the query

    SELECT x.*,
    nvl(txb.group_flag, 'N') AS group_flag,
    nvl(txb.id, -1) AS billings_txs_id,
    NVL(txb.override_flag,'N') as override_flag,
    NVL(txb.advisory_description,'') AS advisory_description,
    NVL(txb.advisory_flag, 'N') AS advisory_flag,
    NVL(txb.repeat_flag, 'N') AS repeat_flag,
    NVL(txb.stereo_flag, 'N') AS stereo_flag,
    NVL(txb.colour_flag, 'N') AS colour_flag,
    NVL(txb.movie_flag, 'N') AS movie_flag,
    NVL(txb.schools_flag, 'N') AS schools_flag,
    NVL(txb.prod_year, 'Unknown') AS prod_year,
    NVL(txb.audiotel_flag, 'N') AS audiotel_flag,
    NVL(txb.signing_flag, 'N') AS signing_flag,
    NVL(txb.widescreen_flag, 'N') AS widescreen_flag,
    NVL(txb.subtitle_type, 'N') AS subtitle_type,
    NVL(txb.exclude_flag, 'N') AS exclude_flag,
    NVL(txb.passed_flag, 'N') AS passed_flag,
    nvl(txb.billings_title, x.ss_box_nam) AS billings_title,
    nvl(txb.passed_flag, 'N') AS billings_passed_flag,
    nvl(txb.changed_flag, 'N') AS changed_flag,
    nvl(txb.time_changed_flag, 'N') AS time_flag,
    nvl(txb.billings_time, (x.ss_box_time_start_num / 1000) * 300) AS billings_time,
    p.ctr_key AS series_id,
    p.prg_auk AS programme_id,
    C2_BILLING.get_billing_title((NVL(txb.id, -1))) AS TXSDESC,
    NVL(txb.billing_publish_flag, 'N') AS billing_publish_flag,
    NVL(txb.pub_bill_publish_flag, 'N') AS pub_bill_publish_flag,
    NVL(txb.more_info_publish_flag, 'N') AS more_info_publish_flag,
    NVL(txb.highlights_publish_flag, 'N') AS highlights_publish_flag,
    NVL(txb.news_publish_flag, 'N')AS news_publish_flag,
    NVL(txb.contact_level, -1) AS contact_level

    FROM (SELECT to_number(vpd.id),
    txs_time,
    sched_time,
    slot_duration,
    vpd.ss_box_nam AS title,
    vpd.ctr_contrt_num,
    vpd.prg_prog_num,
    svc.service_id,
    svc.schedule_class_id,
    vpd.transhist_sched_dtm AS txs_date,
    vpd.Transhist_ID,
    0 AS billings_level,
    0 AS bill_type,
    1 AS update_id,
    '' AS update_note,
    'N' AS update_flag,
    'N' AS billing_flag,
    'N' AS more_flag,
    'N' AS hilite_flag,
    'N' AS news_flag,
    vpd.transhist_sched_dtm,
    vpd.ss_box_cod,
    vpd.ss_box_nam,
    vpd.ss_box_time_start_num
    FROM V_PLANNING_DETS vpd
    JOIN service_classes svc ON (svc.channel_key = vpd.channel_key AND
    svc.channel_key = vpd.transhist_channel_cli_key)
    WHERE svc.service_id = 142
    AND svc.schedule_class_id = 174
    AND vpd.transhist_sched_dtm = to_date('12 Oct 2005')
    AND vpd.ss_box_visible_flg = 1
    AND vpd.ss_box_current_flg = 1) x
    LEFT JOIN txs_billings_dets txb ON (x.ss_box_cod = txb.ss_box_cod AND
    x.service_id = txb.service_id AND x.transhist_sched_dtm = txb.txs_date)
    LEFT JOIN aitprg ap ON (ap.prg_prog_num = x.prg_prog_num)
    LEFT JOIN prg p ON (p.prg_prog_num = ap.prg_prog_num AND p.ctr_key = ap.ctr_key)

    UNION

    SELECT db.id AS id,
    db.billings_time AS txs_time,
    db.billings_time AS sched_time,
    0 AS slot_duration,
    db.billings_title AS title,
    0 AS ctr_contrt_num,
    0 AS prg_prog_num,
    db.service_id,
    db.class_id AS schedule_class_id,
    db.txs_date AS txs_date,
    0 as Transhist_ID,
    0 AS billings_level,
    1 AS bill_type,
    NVL(bu.id, - 1) AS update_id,
    NVL(bu.note, '') AS update_note,
    NVL(bu.archive_flag, 'N') AS update_flag,
    NVL(bu.billing_flag, 'N') AS billing_flag,
    NVL(bu.more_flag, 'N') AS more_flag,
    NVL(bu.hilite_flag, 'N') AS hilite_flag,
    NVL(bu.news_flag, 'N') AS news_flag,
    db.txs_date AS TRANSHIST_SCHED_DTM,
    '' AS ss_box_cod,
    '' AS ss_box_nam,
    0 AS ss_box_time_start_num,
    NVL(db.group_flag, 'N') AS group_flag,
    db.id AS billings_txs_id,
    'N' AS override_flag,
    '' AS advisory_description,
    'N' AS advisory_flag,
    NVL(db.repeat_flag, 'N') AS repeat_flag,
    NVL(db.stereo_flag, 'N') AS stereo_flag,
    NVL(db.colour_flag, 'Y') AS colour_flag,
    NVL(db.movie_flag, 'N') AS movie_flag,
    NVL(db.schools_flag, 'N')AS schools_flag,
    NVL(db.prod_year, '') AS prod_year,
    NVL(db.audiotel_flag, 'N') AS audiotel_flag,
    NVL(db.signing_flag, 'N') AS signing_flag,
    NVL(db.widescreen_flag, 'N') AS widescreen_flag,
    NVL(db.subtitle_code, 'N') AS subtitle_type,
    NVL(db.exclude_flag, 'N') AS exclude_flag,
    'Y' AS passed_flag,
    db.billings_title AS billings_title,
    'Y' AS billings_passed_flag,
    'N' AS changed_flag,
    'N' AS time_flag,
    db.billings_time ,
    0 AS series_id,
    0 AS programme_id,
    C2_BILLING.get_billing_title((NVL(db.id, -1))) AS TXSDESC,
    NVL(db.billing_publish_flag, 'N') AS billing_publish_flag,
    NVL(db.pub_bill_publish_flag, 'N') AS pub_bill_publish_flag,
    NVL(db.more_info_publish_flag, 'N') AS more_info_publish_flag,
    NVL(db.highlights_publish_flag, 'N') AS highlights_publish_flag,
    NVL(db.news_publish_flag, 'N') AS news_publish_flag,
    -1 AS contact_level
    FROM dummy_billings_dets db,
    billing_update_items bu
    WHERE db.service_id = 142
    AND db.class_id = 174
    AND db.txs_date = to_date('12 Oct 2005')
    AND db.id = bu.billing_det_id(+)

    ORDER BY 2


    this is the execution plan for the first part

    SELECT STATEMENT, GOAL = ALL_ROWS Cost=713 Cardinality=13667569 Bytes=10264344319
    NESTED LOOPS OUTER Cost=713 Cardinality=13667569 Bytes=10264344319
    NESTED LOOPS OUTER Cost=144 Cardinality=112 Bytes=51856
    HASH JOIN OUTER Cost=64 Cardinality=112 Bytes=47488
    VIEW Object owner=PIRATE_DEV2 Cost=53 Cardinality=1 Bytes=398
    TABLE ACCESS BY INDEX ROWID Object owner=PIRATE_DEV2 Object name=SERVICE_CLASSES Cost=1 Cardinality=1 Bytes=19
    NESTED LOOPS Cost=53 Cardinality=1 Bytes=119
    REMOTE Cost=52 Cardinality=1 Bytes=100
    INDEX RANGE SCAN Object owner=PIRATE_DEV2 Object name=SERVICE_CLASSES_I4 Cost=0 Cardinality=14
    REMOTE Cost=7 Cardinality=224373 Bytes=5833698
    REMOTE Cost=1 Cardinality=1 Bytes=39
    TABLE ACCESS BY INDEX ROWID Object owner=PIRATE_DEV2 Object name=TXS_BILLINGS_DETS Cost=7 Cardinality=121829 Bytes=35086752
    INDEX RANGE SCAN Object owner=PIRATE_DEV2 Object name=TXS_BILLINGS_DETS_I3 Cost=2 Cardinality=9


    and for the second query

    SELECT STATEMENT, GOAL = ALL_ROWS Cost=85 Cardinality=1 Bytes=300
    HASH JOIN OUTER Cost=85 Cardinality=1 Bytes=300
    TABLE ACCESS FULL Object owner=PIRATE_DEV2 Object name=DUMMY_BILLINGS_DETS Cost=43 Cardinality=1 Bytes=142
    TABLE ACCESS FULL Object owner=PIRATE_DEV2 Object name=BILLING_UPDATE_ITEMS Cost=41 Cardinality=14571 Bytes=2302218


    and now both togerher with the UNION

    SELECT STATEMENT, GOAL = ALL_ROWS Cost=3048313 Cardinality=13667570 Bytes=10264344619
    SORT UNIQUE Cost=2167085 Cardinality=13667570 Bytes=10264344619
    UNION-ALL
    NESTED LOOPS OUTER Cost=713 Cardinality=13667569 Bytes=10264344319
    NESTED LOOPS OUTER Cost=144 Cardinality=112 Bytes=51856
    HASH JOIN OUTER Cost=64 Cardinality=112 Bytes=47488
    VIEW Object owner=PIRATE_DEV2 Cost=53 Cardinality=1 Bytes=398
    TABLE ACCESS BY INDEX ROWID Object owner=PIRATE_DEV2 Object name=SERVICE_CLASSES Cost=1 Cardinality=1 Bytes=19
    NESTED LOOPS Cost=53 Cardinality=1 Bytes=119
    REMOTE Cost=52 Cardinality=1 Bytes=100
    INDEX RANGE SCAN Object owner=PIRATE_DEV2 Object name=SERVICE_CLASSES_I4 Cost=0 Cardinality=14
    REMOTE Cost=7 Cardinality=224373 Bytes=5833698
    REMOTE Cost=1 Cardinality=1 Bytes=39
    TABLE ACCESS BY INDEX ROWID Object owner=PIRATE_DEV2 Object name=TXS_BILLINGS_DETS Cost=7 Cardinality=121829 Bytes=35086752
    INDEX RANGE SCAN Object owner=PIRATE_DEV2 Object name=TXS_BILLINGS_DETS_I3 Cost=2 Cardinality=9
    HASH JOIN OUTER Cost=85 Cardinality=1 Bytes=300
    TABLE ACCESS FULL Object owner=PIRATE_DEV2 Object name=DUMMY_BILLINGS_DETS Cost=43 Cardinality=1 Bytes=142
    TABLE ACCESS FULL Object owner=PIRATE_DEV2 Object name=BILLING_UPDATE_ITEMS Cost=41 Cardinality=14571 Bytes=2302218

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Check if you can do this with UNION ALL.
    what i doubt is union checks for the both queries output and filter them so that no duplicate record is there and if your query returns lot of value couold be sorting is the issue.

    If so you can set the increase PGA and run the query.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by misuk11
    FROM (SELECT to_number(vpd.id),
    txs_time,
    sched_time,
    slot_duration,
    vpd.ss_box_nam AS title,
    vpd.ctr_contrt_num,
    vpd.prg_prog_num,
    svc.service_id,
    svc.schedule_class_id,
    vpd.transhist_sched_dtm AS txs_date,
    vpd.Transhist_ID,
    0 AS billings_level,
    0 AS bill_type,
    Materialize the in-line view by adding rownum.

    FROM (SELECT rownum , to_number(vpd.id),
    txs_time,
    sched_time,
    slot_duration,
    vpd.ss_box_nam AS title,
    vpd.ctr_contrt_num,
    vpd.prg_prog_num,
    svc.service_id,
    svc.schedule_class_id,
    vpd.transhist_sched_dtm AS txs_date,
    vpd.Transhist_ID,
    0 AS billings_level,
    0 AS bill_type,

    Tamil

Posting Permissions

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


Click Here to Expand Forum to Full Width