| QUERY Help [message #527116] |
Sat, 15 October 2011 04:47  |
 |
venkatlvr
Messages: 5
Registered: October 2011
Location: DUBAI
|
Junior Member |
|
|
Below query is taking huge time .. can any one help me to tune this query....
select a.datakey,b.customer_id,'TC',a.flnmth,d.reporting_cur,
fn_ont_get_lc_value(NVL(a.rependdat,a.salmth),a.curcod,d.reporting_cur,(a.rpd_cosnetamt - a.rpd_coscomamt - a.rpd_cosorcamt),(a.cbknetamt - a.cbkcomamt - a.cbkorcamt),a.lc_net_net) lc
from on_cust_tk_cod b,hist_dtls a,on_agt_sql d
WHERE a.partition_id=70 and
(a.flndat BETWEEN b.start_date AND nvl(b.end_date, a.flndat)) AND
b.customer_id = d.customer_id and
(a.tcod||a.tcod001||a.orgtcod like '%' || b.tr_code || '%') and not exists
(select 1 from on_corporate c where c.datakey=a.datakey and c.customer_id = b.customer_id)
EXPLAIN PLAN
object_name cost cardinality bytes
SELECT STATEMENT, GOAL = CHOOSE 124509 1527987 261285777
HASH JOIN RIGHT ANTI 124509 1527987 261285777
TABLE ACCESS FULL ON_CORPORATE 5714 1500440 39011440
MERGE JOIN 103941 1632168 236664360
SORT JOIN 1149 4733 288713
HASH JOIN 1147 4733 288713
TABLE ACCESS FULL ON_CUST_TK_COD 15 4733 179854
TABLE ACCESS FULL ON_AGT_SQL 1131 148515 3415845
FILTER
SORT JOIN 98245 2758787 231738108
PARTITION LIST SINGLE 44363 2758787 231738108
TABLE ACCESS FULL HIST_DTLS 44363 2758787 231738108
[Updated on: Sat, 15 October 2011 04:51] Report message to a moderator |
|
|
|
| Re: QUERY Help [message #527119 is a reply to message #527116] |
Sat, 15 October 2011 05:01  |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Then I think you need to post the query and execution plan properly formatted, so that people can read them.
But before you do that, I noticed that in your original version of your question, the query included thhis attempt at a hint:/*+ Index(IDX_CUST_TRK_CODE_TC)*/
Perhaps better to start again.
[Updated on: Sat, 15 October 2011 05:02] Report message to a moderator |
|
|
|
| Re: QUERY Help [message #527121 is a reply to message #527119] |
Sat, 15 October 2011 05:12 |
|
venkatlvr
Messages: 5
Registered: October 2011
Location: DUBAI
|
Junior Member |
|
|
Hi John ....
Its not considering that hint thats why I removed.
DESCRIPTION object_name cost cardinality bytes
SELECT STATEMENT, GOAL = CHOOSE 124509 1527987 261285777
HASH JOIN RIGHT ANTI 124509 1527987 261285777
TABLE ACCESS FULL ON_CORPORATE 5714 1500440 39011440
MERGE JOIN 103941 1632168 236664360
SORT JOIN 1149 4733 288713
HASH JOIN 1147 4733 288713
TABLE ACCESS FULL ON_CUST_TK_COD 15 4733 179854
TABLE ACCESS FULL ON_AGT_SQL 1131 148515 3415845
FILTER
SORT JOIN 98245 2758787 231738108
PARTITION LIST SINGLE 44363 2758787 231738108
TABLE ACCESS FULL HIST_DTLS 44363 2758787 231738108
[Updated on: Sat, 15 October 2011 05:15] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
