Re: Speed considerations with joins in views

In article <3mucq5$m0j_at_access1.digex.net>

           cobrien_at_access1.digex.net "Cary B. O'Brien" writes:

: In article <3ls4vo$5oi_at_news.fmso.navy.mil>,
: <James_F_Davis_at_NSLC.FMSO.NAVY.MIL> wrote:
: >We have a number of tables which contain codes representing other data. To
: >make it easy on users, we want to make a view which adds the descriptions of
: >these codes. It will outer-join a table to lookup tables containing the
 descriptions.
: >If a user accesses a view, but does not select a field from a joined table,
 does
: >Oracle still perform the join? Does this hurt performance?
:
: We are doing a similar thing and have found that oracle doesn't seem
: to be able to "look through" the view and figure out the correct
: execution plan. Our problem is that some fields are much more selective
: than others, so if you do things in the wrong order, it is very slow.
:

w.r.t James' comments: I have rarely had problems with Oracle "looking through" views to take the access path that I expect; however, I do tend to brief users on what constitutes reasonable and unreasonable use of the views supplied.

It is possible to pick SELECT columns badly in views so that the users' queries _have_ to run badly.
e.g. when creating a view like the following:

	create view dept_emp as
	select
		emp.emp_no,
		emp.dept_no,
		emp.emp_name,
		dep.dept_name
	from
		employees	emp,
		departments	dep
	where
		emp.dept_no = dep.dept_no

you need to consider the possible usage, and the table indexing, to decide whether to select:

	emp.dept_no
	emp.dept_no + 0
	dep.dept_no
	dep.dept_no + 0

and whether to set the join condition to:
	emp.dept_no + 0 = dep.dept_no
	emp.dept_no = dep.dept_no + 0
	emp.dept_no = dep.dept_no

you could even have to consider the best order for the tables in the FROM clause.

Without such considerations, you can easily end up with a view definition which makes it easy for a user to produce an inefficient query.

With careful planning, it is possible to produce views where it is difficult, or at least unreasonable, for them to hammer the system.

BTW: the answer to the original question (If a user accesses a view, but does not select a field from a joined table, does Oracle still perform the join? Does this hurt performance?) is:

	Yes, Oracle will still perform the join, but it may not affect
	the performance very much if the join (i.e. all the relevant 
	conditions in the where clause) can be satisfied in the index 
	used for the join.

-- 
Jonathan Lewis