Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    46

    Unanswered: Indices and views

    Hi,
    I would like to know oracle's behavior in such situations :

    select * from view_name where col1 = 'aaa';

    view_name is a view which looks like :

    select *
    from table1,table2,table3,table4
    where .....
    union
    select *
    from table1,table2,table3,table4
    where ......

    and col1 is a part of table1 and there is an index created on this column.

    Does oracle use an index on col1 ?
    Does oracle always execute select to create view and then performes other operations ?
    Does operation 'union' permit using index on column col1 ??
    Does this operation have to be perform before condition col1 = 'aaa'??

    Thanks for any answers
    Szalas

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Does oracle use an index on col1 ?

    It depends on your optimizer mode. If it is Rule then, It definately use the Index on it. If it is Cost bases then, It evaluates each operation internally for the cost and choose the least expensive.

    Does oracle always execute select to create view and then performes other operations ?

    No, Views are created very first time when you execute CREATE VIEW statemnet but executed when one executes it with appropriate grant on it instead of TABLE itself.


    Does operation 'union' permit using index on column col1 ?

    I cannot say, It depends on the Number of Blocks accessed by this Query. If it returns more than 14-20% of total Blocks, It will use FTS instead Index scan.

    Does this operation have to be perform before condition col1 = 'aaa'??

    First of all, Oracle evaluates the WHERE condition.

    You should read Oracle SQL Performace and Tuning Documents. It helps you more for extra detail.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •