Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    50

    Unhappy Unanswered: Query joining 2 views doesn't use indexes

    Hi all,

    I have 4 tables.
    client_a, client_b, policy_a, policy_b

    key between client tables and policy tables is clientno

    I have 2 views which are
    view1(client_a union client_b)
    view2(policy_a union policy_b)

    i have indexes on the clientno field on all 4 tables and on polno on policy_a and policy_b

    when i run the following query i see that there is full table scans on the client_a and client_b although i have indexes on the clientno field. Can you tell me why it doesn't use these indexes????????????

    Here is the query

    select view1.clientno,surname from
    view1,view2
    where view1.clientno = view2.clientno and view2.polno = '76890'

    Here is the explain plan

    SELECT STATEMENT Optimizer Mode=CHOOSE
    SORT ORDER BY
    HASH JOIN
    VIEW VIEW2
    SORT UNIQUE
    UNION-ALL TABLE ACCESS BY INDEX ROWID POLICY_A INDEX RANGE SCAN POLICY_A_IX02
    TABLE ACCESS BY INDEX ROWID POLICY_B INDEX RANGE SCAN POLICY_B_IX02
    VIEW VIEW1
    SORT UNIQUE
    UNION-ALL
    TABLE ACCESS FULL CLIENT_A
    TABLE ACCESS FULL CLIENT_B

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    how many rows in each view?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    whoa whoa whoa!

    you are selecting from the VIEWS not from the TABLES.
    The indexes are on the TABLES not the VIEWS.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jun 2003
    Posts
    50
    There is 300,000 in each view
    shouldn't the view use the indexes of underlying tables??
    or do i have to create views with hints

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Could you please give the exact definitions of view1 and view2, and check if the indexes are used when you query each view individually or if there are full table scans anyway ?

  6. #6
    Join Date
    Jun 2003
    Posts
    50
    If i query each view, the indexes are used
    the view definitions are as follows

    view1
    clientno varchar2(10)
    surname varchar2(40)
    forename varchar2(20)

    view2
    clientno varchar2(10)
    polno varchar2(10)
    tbmref varchar2(20)

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    I think I got confused.

    did you analyze the tables?
    analyze table client_a compute statistics;
    analyze table client_b compute statistics;
    analyze table policy_a compute statistics;
    analyze table policy_b compute statistics;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    this works for me:
    PHP Code:
    topicadm@Topic_Devcreate table temp_a as select from dba_objects;

    Table created.

    Elapsed00:00:00.04
    topicadm
    @Topic_Devcreate table temp_b as select from dba_objects;

    Table created.

    Elapsed00:00:00.01
    topicadm
    @Topic_Devcreate view t_1 as select object_idobject_nameobject_type
      2  from temp_a
    ;

    View created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devcreate view t_2 as select object_idobject_nameobject_type
      2  from temp_b
    ;

    View created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devset autotrace traceonly explain
    topicadm
    @Topic_Devselect distinct a.object_type
      2  from t_1 a
    t_2 b where a.object_type b.object_type;
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   SORT 
    (UNIQUE)
       
    2    1     MERGE JOIN
       3    2       SORT 
    (JOIN)
       
    4    3         TABLE ACCESS (FULLOF 'TEMP_B'
       
    5    2       SORT (JOIN)
       
    6    5         TABLE ACCESS (FULLOF 'TEMP_A'

    topicadm@Topic_Devcreate index temp_ind1 on temp_a (object_typenologging;

    Index created.

    Elapsed00:00:00.01
    topicadm
    @Topic_Devanalyze table temp_a compute statistics;

    Table analyzed.
    topicadm@Topic_Devcreate index temp_ind2 on temp_b (object_typenologging;

    Index created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devanalyze table temp_b compute statistics;

    Table analyzed.

    Elapsed00:00:00.04
    topicadm
    @Topic_Devselect distinct a.object_type
      2  from t_1 a
    t_2 b where a.object_type b.object_type;
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7178 Card=24 Bytes=336)
       
    1    0   SORT (UNIQUE) (Cost=7178 Card=24 Bytes=336)
       
    2    1     NESTED LOOPS (Cost=6 Card=3003691 Bytes=42051674)
       
    3    2       INDEX (FAST FULL SCANOF 'TEMP_IND1' (NON-UNIQUE) (Cost=4 Card=8490 Bytes=59430)
       
    4    2       INDEX (RANGE SCANOF 'TEMP_IND2' (NON-UNIQUE
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    can I see the create view statements??

    I personally hate UNION (why does your explain plan say 'union all'

    Anyways, why not join the tables instead of the union?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Ok, but could you please give the exact queries defining the two views ? Are they just like ((select * from tableA) UNION (select * from tableB)) ? Are your database statistics up to date ? If they are not, you should try the hint "RULE" :

    SELECT /*+ RULE */ view1.clientno,surname from
    view1,view2
    where view1.clientno = view2.clientno and view2.polno = '76890';

    so as to force Oracle to use the rule-based optimizer. If you want to use the cost-based optimizer efficiently (which is greatly recommended by Oracle), be sure to keep your db stats up to date. If it is the case, you can try :

    SELECT /*+ ALL_ROWS */ view1.clientno,surname from
    view1,view2
    where view1.clientno = view2.clientno and view2.polno = '76890';

    which will tell Oracle to use the cost-based optimizer, optimized to get all the results (ALL_ROWS).

    To update your db stats, use the DBMS_STATS package. For example :

    EXECUTE dbms_stats.gather_database_stats();

    I hope that will help you.

Posting Permissions

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