Re: Oracle views and indexes

John David Birch wrote:

> I have an application written i Power Builder 6.5.
> Complicated Queries involving views and outer joins permeate the
> application.
>
> The application can be used against a variety of DBMS's but runs into
> performance problems when run against Oracle. Oracle 7.3 and Oracle 8.0 seem
> to be about 75 times slower than Sybase ASA. Where as Oracle 8i improves the
> situation by a factor of 3 but still lags behind ASA by a factor of 25.
>
> While the queries could be improved to make use of rule-based-optimizer this
> would involve a major rewrite of the application.
>
> I would prefer to do something in the database such as define the view to
> make use of an index.
>
> Does anyone have any ideas as to how this might be achieved ? Or have any
> suggestions on how else I might tweek the database ?

There are several tings you can do:

1. Look at the explain plan for each of the views.
2. Run ALALYZE on all the tables and indexes.
3. Look at your init.ora params (the ones that deal with complex index usage)
4. Look at your distribution of tablespaces on your server's file systems.

If all else fails, you may need to re-think your design.

--
Ron Reidy
Oracle DBA, Perl Gunslinger
Reidy Consulting, L.L.C. Received on Sun Jun 06 1999 - 23:47:01 CDT