Index Unused in query using DB Link

[ssekhar]

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,

[abhaysk]

restructure your query..it will give results fast.

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.

[ssekhar]

Thanks - It helps...

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


Regards,
Soma