-
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
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|