|
|
|
Junior Member
 
Group: Forum Members
Last Login: 8/31/2008 2:22:45 PM
Posts: 23,
Visits: 99
|
|
the following code is taking about 9-10 hrs to comlpete and most of the times it's going out of spool .the final data inserted is in fig of 10 digits can anybody help me out how to optimize this code?dont consider collect stats as a soln as they are taken care of...
INSERT INTO TMP_T245235B
(
ACCT_ID,
CALL_USAGE_CHRGS,
SRVC_AND_EQUIP_CHRGS,
OTHR_CHRGS,
DT_ID
)
SELECT
TB.ACCT_ID,
(ZEROIFNULL(WRLN_BILL_CT_NET_AM) + ZEROIFNULL(WRLN_BILL_CT_GST_AM)) ,
(ZEROIFNULL(WRLN_BILL_RC_NET_AM) + ZEROIFNULL(WRLN_BILL_RC_GST_AM)) ,
(ZEROIFNULL(WRLN_BILL_NRC_GST_AM)+ ZEROIFNULL(WRLN_BILL_NRC_NET_AM)),
ABSD.DT_ID
FROM
${ETLVMDB}.TMP_T245215 TB
INNER JOIN
${VMDB}.AGG_BILL_SUBTN_DT ABSD
ON
TB.ACCT_ID = ABSD.ACCT_ID;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/26/2008 1:20:52 AM
Posts: 4,
Visits: 8
|
|
|
Try case statements in place of zero if null functions
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Today @ 10:56:35 AM
Posts: 298,
Visits: 363
|
|
Really can't do much without more info, e.g. DDL and EXPLAIN.
Some obvious things to consider:
Is ACCT_NO the PI in any/all of the three tables? Is it unique? Do you get the right answer when the query finally does finish?
|
|
|
|