with/inline view efficiency question

I am using
"Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production"

I am building some views. I am using a "WITH" clause that selects a large set of items, and then the main query joins with the "with" query on one of the fields so as to filter that result to get just the rows that are needed.

I find this much easier to test as the with query can be run as-is to view all the data it would be selecting. Also, it is much less work for me when the main query needs to use multiple columns from the same sub query as the WITH sql is only included once, instead of once per column in an inline view.

The number of columns that the WITH query provides to the main query will vary from view to view. I don't know if that makes any difference to the answer to the question.

However, the view will be used most frequently to display a _single_ row in a form (Oracle forms 10G rel 2) , and so my concern revolves around the efficiency of the WITH query selecting lots of data and then being filtered to just the values in the outer query.

An example of what I _want_ to use (not the actual sql, just to illustrate)

	create view groups_and_their_values
	as
	with summed_values as
	(
		select the_group , sum(the_value) group_total_value
		from the_values
		group by the_group
	)
	select
		a.the_group ,
		a.other_columns_etc ,
		b.group_total_value
	from
		table_a a , 
		summed_values b
	where
		a.the_group = b.the_group

The alternative is to put the sub query into the select itself and only select the required rows

	select
	        a.the_group ,
	        a.another_columns_etc ,
		( select sum( sum(the_value) group_total_value
		  from the_values v
		  where v.the_group = a.the_group
		) group_total_value
	from
        	table_a a 

Is there a reasonable rule of thumb for whether one method is much better than the other in regards to efficiency? Does it even make any difference - I can easily imagine Oracle being smart that this is not a problem at all, but I don't trust myself to test this and get a reliable answer.

Feedback welcome. Received on Mon Jan 09 2006 - 18:26:42 CST