Home » RDBMS Server » Performance Tuning » Optimizing sql
Optimizing sql [message #556631] Tue, 05 June 2012 14:25 Go to next message
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 #556634 is a reply to message #556631] Tue, 05 June 2012 14:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Optimizing sql [message #556636 is a reply to message #556634] Tue, 05 June 2012 15:05 Go to previous messageGo to next message
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 Go to previous message
cookiemonster
Messages: 13894
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.
Previous Topic: Oracle documentation on details of AWR report
Next Topic: how to reduce the clustering_factor's value?
Goto Forum:
  


Current Time: Tue Sep 21 09:20:15 CDT 2021