Re: Oracle Views and Indexes

A copy of this was sent to "Justin Wigg" <justinwigg_at_yahoo.com> (if that email address didn't require changing) On Wed, 24 Nov 1999 10:04:45 +1000, you wrote:

>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:kq1l3s0m6d7sckvla2n1igrktdki6ah4ko_at_4ax.com...
>> A copy of this was sent to "Justin Wigg" <justinwigg_at_yahoo.com>
>>
>> No, there is no such thing as indexes on views. Views will use any and
>all
>> indexes on the underlying tables.
>>
>> In Oracle8i, release 8.1, there are function based indexes (white paper at
>the
>> URL in my signature on this feature) which are indexes on derived columns
>(eg: I
>> can index upper(cname), or "(a+5)/b")
>
>Thanks Thomas... Just one follow-up: the problem I'm having is a view
>performing *very* poorly. When I took a look at the view (*not* coded by
>me - honest!) I realised that it a fourth generation view. ie., this view
>is based on another view which itself is based on two different views which
>in turn each select from a combination of views and tables.
>

views based on views can perform well -- they can perform poor.

do any of the inner views have aggregates? if so -- it'll probably perform poorly when you layer it. we lose the ability to merge view text and predicates with aggregates and the like in many cases (very hard to push down a predicate against the outer view into the inner view, especially if there are psuedo columns, aggregates, etc in the inner views).

In many cases, unrolling the views and creating one view that queries the base table directly will fix the issue.

>Is there any way to tune this up while maintaining the cursor should I
>re-code all the views so that they only select directly from the tables.
>

do you know about explain plan and sql_trace and tkprof? they will be of use to you in your tuning efforts.

>Again, many thanks...

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation