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