Re: Forms Tuning: Index Problem

In article <1993Feb2.034136.26224_at_oracle.us.oracle.com>, mfriedma_at_uucp (Michael Friedman) writes:
|> In article <1kbieoINNc96_at_nz12.rz.uni-karlsruhe.de> rz34_at_uni-karlsruhe.de writes:
|>
|> >Hi!
 

|> >I'm looking for some help in a bit of performance tuning on
|> >a Forms 3.0 form. I think the problem lies in my indices.
 

|> >Background: Oracle v. 6.0.34.2.1, SQL*Plus v. 3.0.11.1.1, PL/SQL v. 1.0.34.0.1
|> > SQL*Forms v. 3.0.16.8.1, and operation system Unix on a
|> > HP/UX 9000 Series 800.
 

|> >My form has as its base table a view:
 

|> >create view person_view as
|> >select u.user_id, u.user_pnr,p.lastname,p.firstname,p.title,p.telefon,
|> > p.str,p.city,p.institut_name,p.room,i.institut_name_1
|> >from institute i, person p, userid u
|> >where u.user_pnr = p.pers_nr(+) and
|> > u.inst_nr = i.inst_nr
 

|> >(The reason for the outer join is that a person may have more than
|> >one userid.)
|>
|> It's always a bit hard to tell from this kind of information, but
|> won't you get the multiple userids even if you don't have the outer
|> join? As far as I can tell, the outer join will get you any
|> user-institute pairs that are not linked with people. This doesn't
|> seem to be what you want.
|>
Thanks for the reply.

Yes, it had been so long since I'd created that view, I'd forgotten what the outer join was for. It is NOT, as I said previously, because a person may have more than one userid. You are right: it got me userinstitute  pairs not linked with people.... and that was NOT what I wanted. (I didn't create the tables and was at the time trying to cover some inconsistencies in data.)

Thanks also to David Hazledine who thought that a predicate referencing a column from an outerjoin table always causes ORACLE to perform a full table scan.

I removed the outer join and my performance increased greatly!

Thanks again!
Karen Schillinger Received on Wed Feb 03 1993 - 10:10:58 CET