Home » RDBMS Server » Performance Tuning » subquery optimization (6 threads merged by bb)
subquery optimization (6 threads merged by bb) [message #501714] Wed, 30 March 2011 19:51 Go to next message
dave987
Messages: 6
Registered: March 2011
Junior Member
I haven't written a whole lot of pl/sql queries as I typically develop against SQL Server databases using t-sql. I wrote a query but it's awfully slow and I'm wondering if there is a better way to accomplish what I've done. The query has 2 subqueries that do SUMs in the select statement and those 2 subqueries rely on a union of 2 queries in the from statement. The person_id from the "people" query in the from clause is tied to the person_id in the subqueries in the select. The query in the from clause is responsible for getting the unique people that I need to sum up their amounts for. Here is the code (I have removed some of the extraneous pl/sql and renamed some things so it's easier to follow). Any ideas on why this is so slow? Are there better ways to do this? Thanks in advance.

select people.person_id, (select SUM(amt) as AmtSum
FROM table1 rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
WHERE rp.f_id = ba.g_id
AND ba.g_id = st.g_id
AND st.g_id = ta.ta(+)
AND rp.h_id = org.i_id(+)
AND st.somecol = 'testing'
AND rp.idate between '01FEB2011' and '01APR2011'
AND rp.person_id = people.person_id) AS c1,
(select SUM(amt)) as AmtSum 
FROM othertable rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
WHERE rp.f_id = ba.g_id
AND ba.g_id = st.g_id
AND st.g_id = ta.ta(+)
AND rp.h_id = org.i_id(+)
AND st.somecol = 'testing'
AND rp.idate between '01FEB2011' and '01APR2011'
AND rp.person_id = people.person_id) AS c2
from (select rp.person_id
FROM table1 rp
WHERE rp.idate between '01FEB2011' and '01APR2011'
AND rp.g_id = 'VAA' 
UNION 
select rp.person_id
FROM othertable rp
WHERE rp.idate between '01FEB2011' and '01APR2011'
AND rp.g_id = 'VAA') people
order by people.person_id;
Re: subquery optimization (6 threads merged by bb) [message #501721 is a reply to message #501714] Wed, 30 March 2011 20:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>(I have removed some of the extraneous pl/sql and renamed some things so it's easier to follow)
& rendered the code to be invalid

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: subquery optimization (6 threads merged by bb) [message #501725 is a reply to message #501721] Wed, 30 March 2011 21:31 Go to previous messageGo to next message
dave987
Messages: 6
Registered: March 2011
Junior Member
Unfortunately, I don't have access to any of those things. I am in the predicament of having to write this query against a gateway service to the Oracle backend and I don't have enough permissions to be able to get what you requested. I guess I was just looking for possible alternatives that might work a bit faster than the way I have my query constructed now. Without having the additional info, would you still be able to tell me what other approaches I could look into? Thanks
Re: subquery optimization (6 threads merged by bb) [message #501726 is a reply to message #501725] Wed, 30 March 2011 22:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Without a test case or knowing what indexes there are or seeing an execution plan or even knowing what table the amt comes from, about all that can be done is to try to eliminate some of the duplication and implicit conversions. The following may have typing errors, since I was unable to test it. With additional information, it might be possible to use one query without two sub-queries.

select nvl (t1.person_id, t2.person_id) person_id, t1.c1, t2.c2 
from   (select rp.person_id, SUM(amt) as c1
        FROM   table1 rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
        WHERE  rp.f_id = ba.g_id
        AND    ba.g_id = st.g_id
        AND    st.g_id = ta.ta(+)
        AND    rp.h_id = org.i_id(+)
        AND    st.somecol = 'testing'
        AND    rp.g_id = 'VAA' 
        AND    rp.idate between TO_DATE ('01FEB2011', 'DDMONYYYY')
                        and     TO_DATE ('01APR2011', 'DDMONYYYY')
        GROUP  BY rp.person_id) t1
full outer join
       (select rp.person_id, SUM(amt) as c2 
        FROM   othertable rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
        WHERE  rp.f_id = ba.g_id
        AND    ba.g_id = st.g_id
        AND    st.g_id = ta.ta(+)
        AND    rp.h_id = org.i_id(+)
        AND    st.somecol = 'testing'
        AND    rp.g_id = 'VAA'
        AND    rp.idate between TO_DATE ('01FEB2011', 'DDMONYYYY')
                        and     TO_DATE ('01APR2011', 'DDMONYYYY')
        GROUP  BY rp.person_id) t2
on t1.person_id = t2.person_id
order  by person_id;

Re: subquery optimization (6 threads merged by bb) [message #501727 is a reply to message #501726] Wed, 30 March 2011 22:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I just noticed that you do not have any join conditions for "table6 cal", so you would be getting a cartesian product.



Re: subquery optimization (6 threads merged by bb) [message #502753 is a reply to message #501727] Fri, 08 April 2011 18:43 Go to previous messageGo to next message
dave987
Messages: 6
Registered: March 2011
Junior Member
thanks for your help. that worked but now i need to modify the query. i'm not sure if this is possible, but i am trying to use that the combined person_id field in a subquery within the select. Here is what I'm trying to do:

select nvl (t1.person_id, t2.person_id) p_person_id, (select SUM(some_amt) from tblTest t where t.person_id = p_person_id) as person_amt, t1.c1, t2.c2 
from   (select rp.person_id, SUM(amt) as c1
        FROM   table1 rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
        WHERE  rp.f_id = ba.g_id
        AND    ba.g_id = st.g_id
        AND    st.g_id = ta.ta(+)
        AND    rp.h_id = org.i_id(+)
        AND    st.somecol = 'testing'
        AND    rp.g_id = 'VAA' 
        AND    rp.idate between TO_DATE ('01FEB2011', 'DDMONYYYY')
                        and     TO_DATE ('01APR2011', 'DDMONYYYY')
        GROUP  BY rp.person_id) t1
full outer join
       (select rp.person_id, SUM(amt) as c2 
        FROM   othertable rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
        WHERE  rp.f_id = ba.g_id
        AND    ba.g_id = st.g_id
        AND    st.g_id = ta.ta(+)
        AND    rp.h_id = org.i_id(+)
        AND    st.somecol = 'testing'
        AND    rp.g_id = 'VAA'
        AND    rp.idate between TO_DATE ('01FEB2011', 'DDMONYYYY')
                        and     TO_DATE ('01APR2011', 'DDMONYYYY')
        GROUP  BY rp.person_id) t2
on t1.person_id = t2.person_id
order  by person_id;


What I am trying to do is to have a subquery that does a SUM for each person found in the original query. I renamed person_id to p_person_id so it wouldn't be ambiguous in the subquery but it's giving me an error saying that the p_person_id identifier is not found. What am I missing? Thank you.
Re: subquery optimization (6 threads merged by bb) [message #502754 is a reply to message #502753] Fri, 08 April 2011 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it's giving me an error
ERROR? What Error? I don't see any error.

How can we reproduce what you have & see?
Re: subquery optimization (6 threads merged by bb) [message #502755 is a reply to message #502754] Fri, 08 April 2011 19:19 Go to previous messageGo to next message
dave987
Messages: 6
Registered: March 2011
Junior Member
ORA-00904: "p_person_id": invalid identifier
Re: subquery optimization (6 threads merged by bb) [message #502756 is a reply to message #502755] Fri, 08 April 2011 19:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
item in SELECT clause must exist within table in FROM clause
SQL> desc t
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME						    VARCHAR2(20)
 MATCH_ID					    NUMBER(3)
 TEAM_ID					    NUMBER(2)

SQL> select foobar from t;
select foobar from t
       *
ERROR at line 1:
ORA-00904: "FOOBAR": invalid identifier

Re: subquery optimization (6 threads merged by bb) [message #502759 is a reply to message #502756] Fri, 08 April 2011 22:04 Go to previous messageGo to next message
dave987
Messages: 6
Registered: March 2011
Junior Member
thank you!
Re: subquery optimization (6 threads merged by bb) [message #502760 is a reply to message #502759] Fri, 08 April 2011 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
same is true in WHERE clause
SQL> select count(*) from t where foobar = 0;
select count(*) from t where foobar = 0
                             *
ERROR at line 1:
ORA-00904: "FOOBAR": invalid identifier
Re: subquery optimization (6 threads merged by bb) [message #503232 is a reply to message #502760] Wed, 13 April 2011 12:05 Go to previous messageGo to next message
dave987
Messages: 6
Registered: March 2011
Junior Member
Thanks for your help. Another question: is it possible to do a full outer join of more than 2 queries in the FROM? If so, how would I join them together? Here is the code I am working on but I'm not sure how to make it work:

select nvl (t1.person_id, t2.person_id) person_id, t1.c1, t2.c2 
from   (select rp.person_id, SUM(amt) as c1
        FROM   table1 rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
        WHERE  rp.f_id = ba.g_id
        AND    ba.g_id = st.g_id
        AND    st.g_id = ta.ta(+)
        AND    rp.h_id = org.i_id(+)
        AND    st.somecol = 'testing'
        AND    rp.g_id = 'VAA' 
        AND    rp.idate between TO_DATE ('01FEB2011', 'DDMONYYYY')
                        and     TO_DATE ('01APR2011', 'DDMONYYYY')
        GROUP  BY rp.person_id) t1
full outer join
       (select rp.person_id, SUM(amt) as c2 
        FROM   othertable rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
        WHERE  rp.f_id = ba.g_id
        AND    ba.g_id = st.g_id
        AND    st.g_id = ta.ta(+)
        AND    rp.h_id = org.i_id(+)
        AND    st.somecol = 'testing'
        AND    rp.g_id = 'VAA'
        AND    rp.idate between TO_DATE ('01FEB2011', 'DDMONYYYY')
                        and     TO_DATE ('01APR2011', 'DDMONYYYY')
        GROUP  BY rp.person_id) t2
full outer join 
       (select rp.person_id, SUM(amt) as c2 
        FROM   othertable2 rp, table2 ba, table3 st, table4 ta, table5 org, table6 cal
        WHERE  rp.f_id = ba.g_id
        AND    ba.g_id = st.g_id
        AND    st.g_id = ta.ta(+)
        AND    rp.h_id = org.i_id(+)
        AND    st.somecol = 'testing'
        AND    rp.g_id = 'VAA'
        AND    rp.idate between TO_DATE ('01FEB2011', 'DDMONYYYY')
                        and     TO_DATE ('01APR2011', 'DDMONYYYY')
        GROUP  BY rp.person_id) t3
on t1.person_id = t2.person_id
order  by person_id;


Thanks!
Re: subquery optimization (6 threads merged by bb) [message #503233 is a reply to message #503232] Wed, 13 April 2011 12:32 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is a simplified example for you. Notice the usage of coalesce instead of nvl in line 1, since there are three values. Alternatively, you could nest two nvl's. Also, notice lines 10 and 15 where the fields to join on were specified, especially the OR condition within parentheses in line 15.

SCOTT@orcl_11gR2> create table a
  2    (id   number,
  3  	amt  number)
  4  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into a values (1, 1)
  3  into a values (2, 2)
  4  into a values (3, 3)
  5  into a values (4, 4)
  6  into a values (1, 1)
  7  into a values (2, 2)
  8  into a values (3, 3)
  9  into a values (4, 4)
 10  select * from dual
 11  /

8 rows created.

SCOTT@orcl_11gR2> create table b
  2    (id   number,
  3  	amt  number)
  4  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into b values (1, 1)
  3  into b values (2, 2)
  4  into b values (5, 5)
  5  into b values (6, 6)
  6  into b values (1, 1)
  7  into b values (2, 2)
  8  into b values (5, 5)
  9  into b values (6, 6)
 10  select * from dual
 11  /

8 rows created.

SCOTT@orcl_11gR2> create table c
  2    (id   number,
  3  	amt  number)
  4  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into c values (1, 1)
  3  into c values (3, 3)
  4  into c values (5, 5)
  5  into c values (7, 7)
  6  into c values (1, 1)
  7  into c values (3, 3)
  8  into c values (5, 5)
  9  into c values (7, 7)
 10  select * from dual
 11  /

8 rows created.

SCOTT@orcl_11gR2> select coalesce (t1.id, t2.id, t3.id) id,
  2  	    t1.c1, t2.c2, t3.c3
  3  from   (select id, sum (amt) c1
  4  	     from   a
  5  	     group  by id) t1
  6  full outer join
  7  	    (select id, sum (amt) c2
  8  	     from   b
  9  	     group  by id) t2
 10  on t1.id = t2.id
 11  full outer join
 12  	    (select id, sum (amt) c3
 13  	     from   c
 14  	     group  by id) t3
 15  on (t1.id = t3.id or t2.id = t3.id)
 16  order  by id
 17  /

        ID         C1         C2         C3
---------- ---------- ---------- ----------
         1          2          2          2
         2          4          4
         3          6                     6
         4          8
         5                    10         10
         6                    12
         7                               14

7 rows selected.

SCOTT@orcl_11gR2>

[Updated on: Wed, 13 April 2011 12:34]

Report message to a moderator

Previous Topic: Tuning Insert
Next Topic: Query Hanging with Sequential Read or Latch free Waits
Goto Forum:
  


Current Time: Thu Mar 28 05:52:44 CDT 2024