Re: Speed considerations with joins in views
Date: 1995/04/18
Message-ID: <798242169snz_at_jlcomp.demon.co.uk>#1/1
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 LewisReceived on Tue Apr 18 1995 - 00:00:00 CEST