Re: views & indexes

You can't in general. The Oracle docs (8.1.5) explicitly differentiates between mergeable and non-mergeable views.

"Using Hints with Views

Oracle does not encourage you to use hints inside or on views (or subqueries). This is because you can define views in one context and use them in another. However,
such hints can result in unexpected plans. In particular, hints inside views or on views are handled differently depending on whether the view is mergeable into the
top-level query.

Should you decide, nonetheless, to use hints with views, the following sections describe the behavior in each case.

     Hints and Mergeable Views

     Hints and Nonmergeable Views

Hints and Mergeable Views

This section describes hint behavior with mergeable views.

Optimization Approaches and Goal Hints

Optimization approach and goal hints can occur in a top-level query or inside views.

     If there is such a hint in the top-level query, that hint is used regardless of any such hints inside the views.

     If there is no top-level optimizer mode hint, then mode hints in referenced views are used as long as all mode hints in the views are consistent.

     If two or more mode hints in the referenced views conflict, then all mode hints in the views are discarded and the session mode is used, whether default or

     user-specified.

Access Method and Join Hints on Views

Access method and join hints on referenced views are ignored unless the view contains a single table (or references another view with a single table). For such
single-table views, an access method hint or a join hint on the view applies to the table inside the view.

Access Method and Join Hints Inside Views

Access method and join hints can appear in a view definition.

     If the view is a subquery (that is, if it appears in the FROM clause of a SELECT statement), then all access method and join hints inside the view are preserved

     when the view is merged with the top-level query.

     For views that are not subqueries, access method and join hints in the view are preserved only if the top-level query references no other tables or views (that is, if

     the FROM clause of the SELECT statement contains only the view).

Parallel Execution Hints on Views

PARALLEL, NOPARALLEL, PARALLEL_INDEX and NOPARALLEL_INDEX hints on views are always recursively applied to all the tables in the referenced view. Parallel execution hints in a top-level query override such hints inside a referenced view.

Hints and Nonmergeable Views

With non-mergeable views, optimization approach and goal hints inside the view are ignored: the top-level query decides the optimization mode.

Since non-mergeable views are optimized separately from the top-level query, access method and join hints inside the view are always preserved. For the same reason,
access method hints on the view in the top-level query are ignored.

However, join hints on the view in the top-level query are preserved since, in this case, a non-mergeable view is similar to a table. "

Martin

Steve wrote:
>
> when I execute a select from a view how do i insure that
> the view select stmt is using indexes?
>
> My view select stmt does include a 'union', reads from three
> different tables.
>
> Does the view select stmt. use the data passed in from the
> select stmt. to the view?
>
> Right now my explain plan shows a full table scan on 2 tables.
> And twice because of the union.
>
> Is there a better way?
>
> Any help appreciated.
>
> Thanks,
Received on Thu Jan 27 2000 - 17:19:35 CST