URGENT:-Problem due to Remote access of a Table in SQL [message #65581] |
Tue, 02 November 2004 01:11 |
sangeeta Prabhu
Messages: 8 Registered: October 2004
|
Junior Member |
|
|
Hi Friends,
I have this query which is taking a long time to execute.I think it is due to the reference of a table in remote schema. I am also specifying the explain plan of the query.
Query:-
Select *
from TSOP_RECIPIENT_INFO TRI
WHERE TRI.RECIPIENT_INFO_ID NOT IN
(SELECT TRI.RECIPIENT_INFO_ID
from TSOP_RECIPIENT_INFO TRI,
CUS.TSOP_TRANSMITTAL@DT.LINK TST
where TRI.RECIPIENT_INFO_ID = ((SOP_LOG_ID_C * 100) + SOP_TRNSMTL_SET_Q))
Explain Plan:-
COST CARD BYTES
SELECT STATEMENT, GOAL = CHOOSE 3.11450042397975E15 369005 51291695
FILTER
TABLE ACCESS FULL ARROW TSOP_RECIPIENT_INFO 14821 369005 51291695
NESTED LOOPS 8440266186 375346 12011072
TABLE ACCESS FULL ARROW TSOP_RECIPIENT_INFO 14821 369005 2214030
REMOTE 22873 1 26
Can anybody suggest how to speed up this query.
Thanks in Adavance
Nandini
|
|
|
|
Re: URGENT:-Problem due to Remote access of a Table in SQL [message #65585 is a reply to message #65583] |
Wed, 03 November 2004 00:11 |
sangeeta Prabhu
Messages: 8 Registered: October 2004
|
Junior Member |
|
|
Hi Andrew,
Thanks for the suggestion. That really helped I used the hint as mentioned below as well as I created a Function Based index. Now,the subquery responds in 45 seconds.But,whenever I execute the both the queries together the query hangs and there is no response.
Kindly, suggest some point so that the whole query responds faster.
Select *
from
TSOP_RECIPIENT_INFO TRI
WHERE
TRI.RECIPIENT_INFO_ID
NOT IN
(SELECT /*+DRIVING_SITE(TST)*/
/*+ index(TST expression_ndx) */
TRI.RECIPIENT_INFO_ID
from
TSOP_RECIPIENT_INFO TRI,
CUS.TSOP_TRANSMITTAL@DT.LINK TST
where
TRI.RECIPIENT_INFO_ID = ((SOP_LOG_ID_C * 100) + SOP_TRNSMTL_SET_Q))
Thanks & Regards
San
|
|
|