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

    Unanswered: Full scan, why ?

    Hi,
    Why does not Oracle use index on table b executing query:

    select * from a,(select b.col2 from b,c where c.col1(+) = b.col1) d where d.col2(+) = a.col2 and a.col3='AAA'

    There are indices on a.col3 and b.col2 and c.col1.
    I know that problem is I use d.col2(+)=a.col2 instead of d.col2=a.col2 but I don't know why Oracle cann't use this index.

    Thank you very much for your ideas.
    Szalas

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

    Re: Full scan, why ?

    Difficult to answer without knowing a lot more. Some queries:

    1) Does the query use the index on b if you remove the outer join on d.col2?

    2) The query is equivalent to this:
    PHP Code:
    select a.*, b.col2 
      from a
    ,b,
     where c
    .col1(+) = b.col1
       
    and b.col2(+) = a.col2
       
    and a.col3='AAA'
    Does that use the index?

    3) What is the output from EXPLAIN PLAN?

    4) How big are the tables?

  3. #3
    Join Date
    Jul 2003
    Posts
    46

    Re: Full scan, why ?

    Originally posted by andrewst
    Difficult to answer without knowing a lot more. Some queries:

    1) Does the query use the index on b if you remove the outer join on d.col2?
    YES

    2) The query is equivalent to this:
    PHP Code:
    select a.*, b.col2 
      from a
    ,b,
     where c
    .col1(+) = b.col1
       
    and b.col2(+) = a.col2
       
    and a.col3='AAA'
    Does that use the index?
    YES, but I can't use this form

    3) What is the output from EXPLAIN PLAN?
    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE
    MERGE JOIN OUTER
    SORT JOIN
    TABLE ACCESS BY INDEX ROWID a
    INDEX RANGE SCAN a_index_col3
    SORT JOIN
    VIEW
    MERGE JOIN OUTER
    SORT JOIN
    TABLE ACCESS FULL b
    SORT JOIN
    TABLE ACCESS FULL c



    4) How big are the tables?
    b - 2 000 000 rows
    a - 300 000 rows
    c - 1 000 000 rows

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Full scan, why ?

    Originally posted by gszalach
    Hi,
    Why does not Oracle use index on table b executing query:

    select * from a,(select b.col2 from b,c where c.col1(+) = b.col1) d where d.col2(+) = a.col2 and a.col3='AAA'

    There are indices on a.col3 and b.col2 and c.col1.
    I know that problem is I use d.col2(+)=a.col2 instead of d.col2=a.col2 but I don't know why Oracle cann't use this index.

    Thank you very much for your ideas.
    Szalas
    You query below:
    select b.col2 from b,c where c.col1(+) = b.col1
    In this query, you are selecting only from table b. And your outer join is c.col1(+) = b.col1
    Which means you can modify this query to
    select b.col2 from b

    Your query and the query below should return the same output:
    PHP Code:
    select a.*,b.col2
    from a
    ,b
    where b
    .col2(+) = a.col2 
    and a.col3='AAA' 
    Check the explain plan of above query.
    Oracle can do wonders !

  5. #5
    Join Date
    Jul 2003
    Posts
    46

    Re: Full scan, why ?

    My query below:
    select b.col2 from b,c where c.col1(+) = b.col1
    in real database is a view which return all columns from b and c so it is not the solution.
    I would like you to focus on the problem with views and outer joins (why full scans in these cases are necessary)
    Szalas

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296

    Re: Full scan, why ?

    Originally posted by gszalach
    My query below:
    select b.col2 from b,c where c.col1(+) = b.col1
    in real database is a view which return all columns from b and c so it is not the solution.
    I would like you to focus on the problem with views and outer joins (why full scans in these cases are necessary)
    Szalas
    Sorry, but this inner query only returns all rows from table B regardless of what table C has just as the earlier poster stated.

    In order to help you we need to understand why you are writing the
    query the way you are. Since you outer-join, but only list columns from
    table B, you will might as well never join.

    Here's a good example:
    PHP Code:
    platform@kod1SELECT COUNT(DISTINCT(cust_id)) FROM customer;
                         
    240

    platform
    @kod1SELECT COUNT(DISTINCT(cust_id)) FROM active_device_mv;
                          
    62

    platform
    @kod1SELECT COUNT(DISTINCT(a.cust_id)) FROM customer aactive_device_mv b
      2  WHERE b
    .cust_id a.cust_id;
                            
    62

    /* this is what you are doing below which is the same as the first query above */
    platform@kod1SELECT COUNT(DISTINCT(a.cust_id)) FROM customer aactive_device_mv b
      2  WHERE b
    .cust_id(+) = a.cust_id;
                           
    240

    platform
    @kod1SELECT COUNT(DISTINCT(a.cust_id)) FROM customer aactive_device_mv b
      2  WHERE b
    .cust_id a.cust_id(+);
                            
    62 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Full scan, why ?

    Originally posted by gszalach
    My query below:
    select b.col2 from b,c where c.col1(+) = b.col1
    in real database is a view which return all columns from b and c so it is not the solution.
    I would like you to focus on the problem with views and outer joins (why full scans in these cases are necessary)
    Szalas
    As you say this is a view, you will have little control on this. But if you have an access on table b, and all that you want to select from b is col2, then you can query on b directly and avoid this join created by view.
    Oracle can do wonders !

Posting Permissions

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