-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|