Optimizing sql [message #556631] |
Tue, 05 June 2012 14:25 |
|
ora_dev_2012
Messages: 2 Registered: June 2012
|
Junior Member |
|
|
Hello all,
I have the following query that needs to be written using Joins.
/*
Tables used:
1. activities: (a_id, n_id, market, total_payment_amount, act_type, act_number, act_seq) --the money paid by the registrant for the market.
2. market_reg (a_id, n_id, market, reg_stage) -- for each market there are registrants
3. market (market) -- stores market details
I need to get sum of total_payment_amount using the condition. I know the query is not opitmized and its not the correct way to get info. I was
wondering if you guys can suggest me a query that will pull the information by using joins
*/
SELECT a.a_id, a.n_id, SUM (a.total_payment_amount)
FROM activities a
WHERE a.market = 'marketname'
AND a.a_id||a.n_id IN
(SELECT mr.a_id||mr.n_id
FROM market_reg mr
WHERE mr.market = 'marketname'
AND mr.reg_stage = 'P'
AND mr.n_id 0)
AND (a.act_type = 'A'
OR (a.act_type IS NULL
AND a.act_number||act_seq IN
( SELECT a1.act_number||a1.act_seq
FROM activities a1
WHERE a1.market = 'marketname'
GROUP BY a1.act_number||a1.act_seq
HAVING COUNT (a1.act_number||a1.act_seq) = 1)))
GROUP BY a.a_id, a.n_id;
Any kind of help is greatly appreciated
Thanks
MM
[Updated on: Tue, 05 June 2012 14:30] Report message to a moderator
|
|
|
|
Re: Optimizing sql [message #556636 is a reply to message #556634] |
Tue, 05 June 2012 15:05 |
|
ora_dev_2012
Messages: 2 Registered: June 2012
|
Junior Member |
|
|
Thanks for the reply. I am sorry, the title I posted was confusing. what i was wanting was to re-write the sql using joins and not using sub-query. Since I am not very familiar with plan and output from SQL_TRACE & tkprof. But I have formatted the code below. Please let me know if that helps.
SELECT a.a_id, a.n_id, SUM (a.total_payment_amount)
FROM activities a
WHERE a.market = 'marketname'
AND a.a_id || a.n_id IN
(SELECT mr.a_id || mr.n_id
FROM market_reg mr
WHERE mr.market = 'marketname'
AND mr.reg_stage = 'P'
AND mr.n_id <> 0)
AND (a.act_type = 'A'
OR (a.act_type IS NULL
AND a.act_number || act_seq IN
( SELECT a1.act_number || a1.act_seq
FROM activities a1
WHERE a1.market = 'marketname'
GROUP BY a1.act_number || a1.act_seq
HAVING COUNT (a1.act_number || a1.act_seq) = 1)))
GROUP BY a.a_id, a.n_id; /*
Tables used:
1. activities: (a_id, n_id, market, total_payment_amount, act_type, act_number, act_seq) --the money paid by the registrant for the market.
2. market_reg (a_id, n_id, market, reg_stage) -- for each market there are registrants
3. market (market) -- stores market details
*/
[Updated on: Tue, 05 June 2012 15:08] Report message to a moderator
|
|
|
Re: Optimizing sql [message #556739 is a reply to message #556636] |
Wed, 06 June 2012 06:13 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It's very difficult to rewrite SQL when you don't know the table structures, the relationships between the tables, or what the query is supposed to do.
However, you can, and should, get rid of the ||
For example:
AND a.act_number || act_seq IN
( SELECT a1.act_number || a1.act_seq
Should be:
AND (a.act_number, act_seq) IN
( SELECT a1.act_number, a1.act_seq
Doing that may well improve the performance.
|
|
|