Re: Index Usage with VIEWS

hsing_at_ix.netcom.com (Peter Y. Hsing) wrote:
>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