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

    Unanswered: Problem with view and union

    Hi,
    I have another problem :
    I've created view v1 as (select c1,..,ck from t1 union select c1,...,ck from t2).
    And when i perform query like this :
    select * from v1 where c1 like 'asd%' and i have indices on c1 in both tables Oracle does not use this index.
    Does anybody know why ?? Should I set any initialization parameter ??
    Would Oracle use index if I had only index on table t1??
    I uses Oracle 8i and rule optimizer
    Thanks for any ideas
    Szalas

  2. #2
    Join Date
    Jul 2003
    Posts
    46

    Re: Problem with view and union

    It is 'union all' instead of 'union'
    Sorry for my mistake but problem remains
    Szalas

  3. #3
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    Hi ,

    Can you try recreating the view with the following

    create view v1 as (select c1,..,ck from t1 where c1 like 'asd%' union all select c1,...,ck from t2 where c1 like 'asd%').

    then query v1, index should get used.

    When u query a view, first the query used to create the view will be executed. So that time index is not used. when u use WHERE clause on the view, it will be applied on the results of the UNION query. So thats why index might not have used.
    Regards
    Suneel

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Re: Problem with view and union

    Originally posted by gszalach
    It is 'union all' instead of 'union'
    Sorry for my mistake but problem remains
    Szalas
    I know this problem with indices and view unions. The work around is select * from v1 order by <<fieldname>> should take care...
    This is the simplest way

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with view and union

    That's not what I find on 8.1.7.3.0:

    SQL> create or replace view tt_view as
    2 select * from tt1 union select * from tt2
    3 /

    View created.

    SQL> alter session set optimizer_mode=RULE;

    Session altered.

    SQL> select * from tt_view where object_name like 'ABC%';

    no rows selected


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=RULE
    1 0 VIEW OF 'TT_VIEW'
    2 1 SORT (UNIQUE)
    3 2 UNION-ALL
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TT1'
    5 4 INDEX (RANGE SCAN) OF 'TT1_IDX' (NON-UNIQUE)
    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'TT2'
    7 6 INDEX (RANGE SCAN) OF 'TT2_IDX' (NON-UNIQUE)

    BTW, why are you using RBO anyway?

Posting Permissions

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