Re: Speed considerations with joins in views

Cary B. O'Brien (cobrien_at_access1.digex.net) wrote:
: 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.
 

: We found that in many cases we had to re-write the query to explicitly
: reference the underlyng tables in the correct order rather than do the
: query on the view. Even with the optimizer. So watch out.
 

: Explain plan and tkprof were invaluable in sorting this out, since in
: our case the queries were buried in a lot of PL/SQL code.
 

: BTW -- there is a ORATCL/TK tool called oddis that will allow you to do
: an explain plan at the click of a button and see the formatted
: results immediatly. (If you have unix+x+tcl+tk+oratcl)
 

: You can obtain ODDIS from
 

: ftp://ftp.informatik.uni-hannover.de/software/oddis-1.0.tar.gz

: Cary O'Brien
: cobrien_at_access.digex.net

I concur-

We usually are extremely careful to create the views explicitly so that they are optimized with rows in the desired order etc.

Randy

--

_/_/_/_/  _/     _/  _/      _/  _/_/_/  _/_/_/_/   _/_/_/    _/_/_/
_/        _/_/   _/   _/     _/    _/    _/    _/  _/    _/  _/    _/
_/_/_/    _/ _/  _/    _/   _/     _/    _/_/_/    _/        _/    _/
_/        _/  _/ _/     _/ _/      _/    _/        _/        _/    _/
_/_/_/_/  _/   _/_/      /_/     _/_/_/  _/         _/_/_/   _/_/_/
=====================================================================
    Randy DeWoolfson  -  ergo sum    :-)        envipco_at_cais.com
 or Ron Frederick     -  ergo ?      (8P
---------------------------------------------------------------------
#include <StandardDisclaimerOnOpinions>  // Null if file not found.
=====================================================================