Index Unused in query using DB Link
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Index Unused in query using DB Link

  1. #1
    Join Date
    Aug 2002
    Posts
    19

    Index Unused in query using DB Link

    Hi All,

    I have two databases - db1 and db2 and there is a db link from db1 to db2

    DB1 consists of a table T1 which has index on 3columns(col1,col2,col3).

    DB2 consists of a table T2 which has 3columns(col1,col2,col3) as Primary Key and hence Index is created.

    While I issue the query as follows - The result is displayed very fast in 7.39 seconds

    Select t1.col1,t1.col2,t2.col4,t2.col5, t1.col6
    From t1, t2
    Where t1.col1 = t2.col1
    And t1.col2 = t2.col2
    And t1.col3 = t2.col3
    And t1.col1 = 'XXX'
    And t1.col7 = 123

    But when I issue the below query, it takes more than 30minutes to display -

    Select t1.col1,t1.col2,t2.col4,t2.col5, sum(t1.col6)
    From t1, t2
    Where t1.col1 = t2.col1
    And t1.col2 = t2.col2
    And t1.col3 = t2.col3
    And t1.col1 = 'XXX'
    And t1.col7 = 123
    Group BY t1.col1,t1.col2,t2.col4,t2.col5

    Explain Plan for Second Query shows that Index being Used on T1 and Nested Loop(REMOTE Est. Rows:10,201)

    The question is, why does the Explain Plan does not show if any Index Used on T2@DB2 which has Remote Connection by DBLink.

    Any advice..


    Regards,
    Soma

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    restructure your query..it will give results fast.

    Code:
    Select A.col1,A.col2,A.col4,A.col5, SUM(A.col6)
    From 
    (
    Select t1.col1,t1.col2,t2.col4,t2.col5, t1.col6
    From t1, t2
    Where t1.col1 = t2.col1
    And t1.col2 = t2.col2
    And t1.col3 = t2.col3
    And t1.col1 = 'XXX'
    And t1.col7 = 123
    ) A
    Group by A.col1,A.col2,A.col4,A.col5;
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Aug 2002
    Posts
    19
    Thanks - It helps...

    But why I cannot still see the SQL Path Used to QUery from REMOTE DATABASE..


    Regards,
    Soma
    Soma

Posting Permissions

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



Click Here to Expand Forum to Full Width