Re: Tuning View

In article <595rkf$sgf_at_sigov1.sigov.si> Dušan Pirc <Dussan.Pirc_at_mop.sigov.mail.si> had a small problem with Oracle Tuning.

I think the key to better performance is to force the optimizer to use the small local table as "driving" table for the execution of the statement. There are two possibilities to force the optimizer. Either change the table order in the select statement of the view because the optimizer uses the tables in reverse order when no special index can be used. Then you have
 create view view1
 as select ID, attr2, attr2, ... from table2, table1  where KID1 = KID2;

The other way is to use an optimizer hint like  create view view1
 as select /*+ ORDERED */ ID, attr2, attr2, ... from table1, table2  where KID1 = KID2;
or
 create view view1
 as select /*+ USE_NL ( table1 table2 ) */ ID, attr2, attr2, ... from table1, table2
 where KID1 = KID2;

Oliver Received on Sat Dec 28 1996 - 00:00:00 CST