Re: Index Usage with VIEWS

Frank:

>Q1: Why is the access path different for a view than a table.

The indices are not used because of how Oracle parses this view. The UNION clause will force full-table scans in your case, because Oracle cannot concatenate or use separate indices of different tables in a UNION.
>Q2: Is there a way to force the optimizer to choose 'index-scan' of the base
> tables of the view.

Yes, if the users' "where" statements were inside each "select" clause, then, the indices would be used (but you knew that).

As a work-around, I suggest that, barring consolidating the tables together (why are they separate, anyway?), to build dynamic views with dynamic "select" statements, i.e. write a script which builds each user a view of the data at execution of each query.

-Peter

On Mon, 20 May 1996 20:31:42 -0400, Frank Schittone <fschitto_at_cybercomm.net> wrote:

>Hi Everyone:
>
>I have a following situation in an Data Warehousing engagement:
>
> I am dealing with 12 monthly tables each containing 'detail/raw' sales
>data for a month. The table structures of these monthly table is as follows:
>
> ------------------------------------------------
> | c1 | c2 | c3 | ........................| c15 |
> ------------------------------------------------
>
> Each of these monthly table contain 1/2 Million rows. Each of these
>tables have indexes on column c2 (representing revenue in $). We also have a
>view 'V1' representing a yearly unified view of the monthly data:
>
> CREATE VIEW v1 AS
> SELECT * FROM table_month1
> UNION
> SELECT * FROM table_month2
> UNION
> .......
> SELECT * FROM table_month12;
>
> Most of the users will be executing adhoc queries against the view
>rather than the monthly detail tables. Also most of the queries deal will
>AGGREGATE's (EX: sum, max, min etc..)
>
> When I execute a aggregate query(see below) against the view, it does
>a FULL TABLE SCAN as opposed to INDEX SCAN of each of the monthly table even
>though the tables are indexed by aggregate column(c2):
>
> SELECT MAX(c2) FROM v1;
>
> We all know that when we do the following it will use index scan
>because of index on c2 column:
>
> SELECT MAX(c2) FROM table-month1;
>
>
>Q1: Why is the access path different for a view than a table.
>
>Q2: Is there a way to force the optimizer to choose 'index-scan' of the base
> tables of the view.
>
>Note: Optimizer ignores any hints at view level. It is also smart enough!! to
>recognize a 'dummy where clause(where c2 = c2)' and therefore not use this as
>basis of index-scan.
>
>Any comments/suggestions would be appreciated.
>
>
>Please Reply to "fschitto_at_cybercomm.net"
>
>
>Thank You for your time
>--
Received on Tue May 21 1996 - 00:00:00 CEST