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(+)
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.
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,
Reply With Quote
Originally Posted by misuk11
Bookmarks