Thanks for the question, Seema.
Asked: February 24, 2016 - 6:43 am UTC
Last updated: February 24, 2016 - 1:24 pm UTC
Version: 11.2
Viewed 1000+ times
You Asked
Hi tom,
I have a question regarding the internals (and costs) of a UNION ALL statement.
SELECT a.brn,
'IC' module,
a.prod,
a.brn || a.prod || a.ccy || lpad(a.frm_no, 5, '0') || 'A' refno, --diff
a.acc crn,
a.ccy,
a.cur_run_accr amt, --diff
b.cust_no relcust,
acc relacc,
a.ent_dt val_dt --diff
FROM ictbs_entries_history a, sttms_cust_account b
WHERE a.brn = b.branch_code
and a.acc = b.cust_ac_no
and a.accr = 'Y' --diff
and a.entry_type = 'I'
union all
SELECT a.brn,
'IC' module,
a.prod,
a.brn || a.prod || a.ccy || lpad(a.frm_no, 5, '0') || 'L' refno, --diff
a.acc crn,
a.ccy,
amt amt, --diff
b.cust_no relcust,
acc relacc,
a.ent_dt + 1 val_dt --diff
FROM ictbs_entries_history a, sttms_cust_account b
WHERE a.brn = b.branch_code
and a.acc = b.cust_ac_no
and a.liqn = 'Y' --diff
and a.entry_type = 'I
with above query i tried to run query as select * from ( above query ) but no reduction in cost.. can you help to re write this in better way ..
and Chris said...
You're saying that you replaced the columns in your selects above with a *, right?
This is unlikely to improve performance. It's more likely to make things worse. Because:
- Oracle may need to access the table, instead of answering the query from an index. For example, in your query above cust_no is the only column you've selected from sttms_cust_account. If you have an index on (branch_code, cust_ac_no, cust_no) Oracle can use just this index. Change it to a * and it has to access the table (assuming there's more columns in the table).
- You're (probably) fetching more columns. This means you're processing more data. This won't affect the cost (unless the first point applies). But there is more to send over the network, etc.
I don't have enough information to say whether there's a better way to write this. If you want help with performance, then you need to post an execution plan for your query:
https://oracle-base.com/articles/9i/dbms_xplan Make sure you include the /*+ gather_plan_statistics */ hint. We also need some more background information. In particular - are the two select guaranteed to return different rows? If a.accr = 'Y', is a.liqn <> 'Y' (and vice-versa)?
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment