Views and Indexes

[wf]

I've created a view extracting rows from one table. Then I created another view which calculates a value. Then I created a third view that makes a join of these previous views.

The problem is that accessing the data is too slow. I'm accessing it from Visual Interdev sending some selection criteria. But it is so slow that sometimes I get a "connection time out" message.

Also, I don't know if I can create indexes to the views, or if the indexes of the original table are applied to the views.

Any help?

 

[karluk]

No indexes on views are possible, but the indexes on the underlying table can generally help view performance.

 

[jimbopalmer]

The Oracle optimizer can generally help views, but almost always fails to optimize views of views. Any apps that uses views of views is slow in my experience, it is always worth while to write out a view in full, rather than use views of views.

 

[sem]

If you have recent statistics gathered and use CHOOSE it does not matter whether you use view or select. The only difference is the need in reparsing, though it generally takes not too much time if having your dictionary cached.