-
Performance impact of in-line view
Hi,
I try to use inline-view in the following query, but I am not sure if there is a better way to do this, because this query is heavily used in an web application, I want to make sure it doesn't run more than 1 second. Any suggestions are welcome.
Thanks a lot in advance!
Code:
Select
table1.col2,
table1.col3,
table3.col15,
CASE WHEN (Sum(table4.col16)> 0) THEN
(Sum(table4.col17)/Sum(table4.col18)) * 100
ELSE
0
END,
COUNT(invw1.table5_ID),
COUNT(invw2.table5_ID),
COUNT(invw3.table5_ID)
From table1, table2, table3, table4,
(select table5_ID, col7 from table5) invw1,
(select table5_ID, col7 from table5 where col19 = 'XY') invw2,
(select table5_ID, col7 from table5 where col19 = 'YZ') invw3
WHERE table1.table1_ID=table2.table2_ID(+)
AND table1.col3=table4.col4(+)
AND table1.col5=table3.col6(+)
AND table1.col3=invw1.col7(+)
AND table1.col3=invw2.col7(+)
AND table1.col3=invw3.col7(+)
AND table1.col8 = 'AB'
AND table1.col9 = 'DB'
AND table2.col10 = 'CD'
AND table2.col11 = 'DE'
AND table2.col12 = 'EF'
AND table3.col13 = 'FG'
AND table3.col14 = 'HI'
GROUP BY table1.col2, table1.col3
ORDER BY table1.col2
-
Hi, just want to mention that I check Tom's Effective Oracle by Design, on Page 510 - 511, he mentioned that performance of scalar subquery is better than inline view, so I change the query to the following, any comments are welcome. Thanks
Code:
select
table1.col2,
table1.col3,
table3.col15,
CASE WHEN (Sum(table4.col16)> 0) THEN
(Sum(table4.col17)/Sum(table4.col18)) * 100
ELSE
0
END,
(select COUNT(1) from table5 where table5.col7 = table1.col3),
(select COUNT(1) from table5 where table5.col7 = table1.col3 and col19 = 'XY'),
(select COUNT(1) from table5 where table5.col7 = table1.col3 and col19 = 'YZ')
From table1, table2, table3, table4,
WHERE table1.table1_ID=table2.table2_ID(+)
AND table1.col3=table4.col4(+)
AND table1.col5=table3.col6(+)
AND table1.col8 = 'AB'
AND table1.col9 = 'DB'
AND table2.col10 = 'CD'
AND table2.col11 = 'DE'
AND table2.col12 = 'EF'
AND table3.col13 = 'FG'
AND table3.col14 = 'HI'
GROUP BY table1.col2, table1.col3
ORDER BY table1.col2
-
I really doubt that Tom said "scalar subqueries are better than inline views" without qualifying that to give specific circumstances in which that is true.
Inline views are neither good nor bad for performance, they are just a feature. How well (or not) does your query perform right now?
-
Let me see the trace files for both queries.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|