Re: Speed considerations with joins in views

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 Received on Mon Apr 17 1995 - 00:00:00 CEST