Optimizing with views

Hello,

I have an Oracle database application which is built on having a number of view "on top" of the tablestructure, for reading. The performance of the SQL-readings are to poor, and my job is to optimize the view-readings.

The client is built in MS Access using Visual Basic For Applications (DAO-objects), but that code should not be optimized in this first step.

I have a few questions:

How does indexes on views work ? Are they the same as the indexes on the base tables that the views are built from ? Could you put separate indexes on the views ?

Does the Oracle optimizer recognise and use all the indexes of the base tables when you are reading from the views, as it does when you are reading from the base tables ?

I have a few options/approaches for the optimizing job:

• Rewrite the views
• Modify indexes on tables/views (by use of EXPLAIN PLAN)
• Database parameters
• Tablespace, block structure and other table parameters
• Optimizer tuning (ANALYZE TABLE e t c)

Which one of this options does normally give the best result ? What is the best apporoach initially ? Are there any other approaches that I should begin with ?

Thankful for any advice from anyone who have experience in this.

Regards

//
Stefan Nilsson
Sigma Exallon Information AB
email: stefan.nilsson_at_ellips.sydkraft.se Received on Wed Jul 23 1997 - 00:00:00 CEST