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