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