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  |
 |
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 #501726 is a reply to message #501725] |
Wed, 30 March 2011 22:08  |
 |
Barbara Boehmer
Messages: 9100
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 #502753 is a reply to message #501727] |
Fri, 08 April 2011 18:43 |
|
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 #503232 is a reply to message #502760] |
Wed, 13 April 2011 12:05 |
|
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
|
|
Goto Forum:
Current Time: Sun Nov 24 21:06:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
