| Why the query is taking so long to execute [message #65594] |
Thu, 04 November 2004 00:23  |
sangeeta Prabhu
Messages: 8
Registered: October 2004
|
Junior Member |
|
|
Hi,
I have this SQL statement which is not at all responding.
When I execute the sub-query it responds in 16 seconds but when the whole query is executed it doesn't responds at all.I dont understands why when sub-query is executing in 16 seconds than the whole should at least respond in 1 or 2 hours. Can anybody give any suggestions Please to speed up this query.
I am copying the explain plan of the query.
Select /*+PARALLEL(TRI,4) */
/*+ FIRST_ROWS */
*
from
TSOP_RECIPIENT_INFO TRI
WHERE
TRI.RECIPIENT_INFO_ID
NOT IN
(SELECT /*+USE_HASH(TRI TST) */
/*+DRIVING_SITE(TST)*/
/*+ index(TST expression_ndx) */
/*+INDEX_ASC(TRI TEMP_TRECPT_COMPOSE_PNDX) */
TRI.RECIPIENT_INFO_ID
from
TSOP_RECIPIENT_INFO TRI,
CUS.TSOP_TRANSMITTAL@DT.LINK TST
where
TRI.RECIPIENT_INFO_ID = ((TST.SOP_LOG_ID_C * 100) + TST.SOP_TRNSMTL_SET_Q))
COST CARD BYTES
SELECT STATEMENT, GOAL = FIRST_ROWS 3328797811 369005 51660700
FILTER
TABLE ACCESS FULL ARROW TSOP_RECIPIENT_INFO 3706 369005 51660700
HASH JOIN 9021 375346 12386418
INDEX FULL SCAN ARROW TEMP_TRECPT_COMPOSE_PNDX 26 369005 2583035
REMOTE 4575 7506912 195179712
kindly, help as this is urgent.
thanks in advance
Milind
|
|
|
|
| Re: Why the query is taking so long to execute [message #65595 is a reply to message #65594] |
Thu, 04 November 2004 01:21  |
AlanP
Messages: 4
Registered: October 2003
|
Junior Member |
|
|
You could try the following
1) The join to TSOP_RECIPIENT_INFO is unnecessary in the subquery in your example.
2) Try not exists instead of not in
3) Try an outer join between the two tables i.e.
select * from x, y where x.id=y.id(+) and y.id is null
4) is there an index on recipient_info_id?
Alan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
