Home » SQL & PL/SQL » SQL & PL/SQL » Update Query (Oracle Apps)
Update Query [message #297280] Wed, 30 January 2008 23:57 Go to next message
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
Junior Member

Hi,

I have two database instances. One is reside in India and another is at US. I have created one custom table in us instance. In that table I have taken columns from both instances. Now first of all I am inserting data only for us columns from US instances and then I am trying to update columns of Indian instance one by one using dblink. My update query is look like this :

UPDATE ARC.ARC_CRC US_CUST
SET ORDER_NUMBER = (SELECT /*+INDEX(US_CUST IND_TRX_NUMBER) */ IND_CTA.CT_REFERENCE
FROM AR.RA_CUSTOMER_TRX_ALL@QU.COM IND_CTA
WHERE IND_CTA.TRX_NUMBER = US_CUST.CTA_TRX_NUMBER
AND EXISTS(SELECT 'X'
FROM AR.RA_CUSTOMER_TRX_ALL@QU.COM IND_CTA1,ARC.ARC_CRC US_CUST
WHERE IND_CTA1.TRX_NUMBER = US_CUST.CTA_TRX_NUMBER))

I have approx. 1,00,000 data available thats y this update query takes more than 2 hours to run...

This whole process is specified by PM thats y I cannot use materialized view.
Is there any other solutions to do the same?

Thanks............
Re: Update Query [message #297300 is a reply to message #297280] Thu, 31 January 2008 01:19 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Don't insert populating some of the columns then UPDATE the others. You must INSERT all columns at once. You need to perform a JOIN.

Ross Leishman
Re: Update Query [message #297317 is a reply to message #297300] Thu, 31 January 2008 02:12 Go to previous messageGo to next message
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
Junior Member

Thanks for your valuable reply.

But my problem is if I am trying to insert all columns once then it is taking more than 5 hours for inserting data due to lacks of data and another main reason is due to more than 12 dblinks....

Thats y our PM told me that do like this...

Is there any other solutions for the populating data in custom table for both the instances. I have tried no. of hint to minimize time required to execute query but it not working.
Re: Update Query [message #297466 is a reply to message #297317] Thu, 31 January 2008 20:01 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
12 database links? You showed us 2.

You wouldn't have shown us only part of the problem would you? Because that would mean you would get answers to the wrong question.
Re: Update Query [message #297473 is a reply to message #297466] Thu, 31 January 2008 21:56 Go to previous messageGo to next message
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
Junior Member

This is my US instance query :

SELECT US_CUST.CTA_TRX_NUMBER INVOICE_NUMBER,
US_CUST.CTA_TRX__DATE INVOICE_DATE,
US_CUST.DUE_DATE DUE_DATE,
US_CUST.DAYS_LATE DAYS_LATE,
US_CUST.GL_DATE GL_DATE,
US_CUST.AGING AGING,
IND_RCTA.CT_REFERENCE ORDER_NUMBER,
IND_OH.CUST_PO_NUMBER CUSTOMER_PO,
US_CUST.BILL_TO_CUSTOMER_NO BILL_TO_CUSTOMER_NO,
US_CUST.BILL_TO_CUSTOMER_NAME BILL_TO_CUSTOMER_NAME,
IND_HCSU.LOCATION BILL_TO_LOCATION,
US_CUST.BILL_TO_ADDRESS BILL_TO_ADDRESS,
IND_HCA.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NO,
IND_HP.PARTY_NAME SHIP_TO_CUSTOMER_NAME,
IND_HCSU1.LOCATION SHIP_TO_LOCATION,
IND_HL.ADDRESS1 ||' ' || IND_HL.ADDRESS2 ||' '|| IND_HL.ADDRESS3 || ' ' ||IND_HL.PROVINCE||' '|| IND_HL.CITY ||' '|| IND_HL.STATE ||' '|| IND_HL.POSTAL_CODE ||' '||IND_HL.COUNTRY SHIP_TO_ADDRESS,
IND_RCTA.ATTRIBUTE15 GC_NOTE,
IND_OH.FREIGHT_CARRIER_CODE TRANSPORTER,
IND_RC.NAME REGION,
US_CUST.ORIGINAL_AMOUNT AMOUNT_DUE_ORIGINAL,
US_CUST.APPLIED_AMOUNT AMOUNT_APPLIED,
US_CUST.REMAIN_AMOUNT AMOUNT_DUE_REMAINING,
DECODE(IND_APS.INVOICE_CURRENCY_CODE,'INR',(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_ORIGINAL,0)) * 0.98) + SUM(NVL(IND_APS.TAX_ORIGINAL,0)),(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_ORIGINAL,0))*(NVL(IND_APS.EXCHANGE_RATE,1)) * 0.98) + (SUM(NVL(IND_APS.TAX_ORIGINAL,0))*(NVL(IND_APS.EXCHANGE_RATE,1)))) LINE_AMT_98_TAX, --ONLY NON-INR TAX AMOUNT IS CONVERTED USING EXCHANGE RATE
DECODE(IND_APS.INVOICE_CURRENCY_CODE,'INR',(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_ORIGINAL,0))*0.02),(SUM(NVL(IND_APS.AMOUNT_LINE_ITEMS_O RIGINAL,0))*(NVL(IND_APS.EXCHANGE_RATE,1))*0.02)) "2% BILL AMOUNT"
FROM
ARC.XX_CRC_PRC_REP US_CUST,
AR.AR_PAYMENT_SCHEDULES_ALL@QU.COM IND_APS,
AR.RA_CUSTOMER_TRX_ALL@QU.COM IND_RCTA,
ONT.OE_ORDER_HEADERS_ALL@QU.COM IND_OH,
AR.HZ_CUST_SITE_USES_ALL@QU.COM IND_HCSU ,
AR.HZ_CUST_ACCOUNTS@QU.COM IND_HCA,
AR.HZ_PARTIES@QU.COM IND_HP,
AR.HZ_CUST_ACCT_SITES_ALL@QU.COM IND_HCAS,
AR.HZ_CUST_SITE_USES_ALL@QU.COM IND_HCSU1,
AR.HZ_LOCATIONS@QU.COM IND_HL,
AR.HZ_PARTY_SITES@QU.COM IND_HPS1,
AR.HZ_CUST_ACCT_SITES_ALL@QU.COM IND_HCAS1,
AR.AR_COLLECTORS@QU.COM IND_RC,
AR.HZ_CUSTOMER_PROFILES@QU.COM IND_HCP,
ONT.OE_TRANSACTION_TYPES_TL@QU.COM IND_OTT
WHERE IND_APS.TRX_NUMBER = US_CUST.CTA_TRX_NUMBER
AND IND_APS.CUSTOMER_TRX_ID = IND_RCTA.CUSTOMER_TRX_ID
AND US_CUST.CTA_TRX_NUMBER = IND_RCTA.TRX_NUMBER
AND TO_CHAR(IND_OH.ORDER_NUMBER) = IND_RCTA.CT_REFERENCE
AND IND_HCSU.SITE_USE_ID=IND_RCTA.BILL_TO_SITE_USE_ID
AND IND_HP.PARTY_ID=IND_HCA.PARTY_ID
AND IND_HCA.CUST_ACCOUNT_ID=IND_HCAS.CUST_ACCOUNT_ID
AND IND_HCAS.CUST_ACCT_SITE_ID=IND_HCSU.CUST_ACCT_SITE_ID
AND IND_HCSU1.SITE_USE_ID = IND_RCTA.SHIP_TO_SITE_USE_ID
AND IND_HL.LOCATION_ID = IND_HPS1.LOCATION_ID
AND IND_HCAS1.CUST_ACCT_SITE_ID = IND_HCSU1.CUST_ACCT_SITE_ID
AND IND_HPS1.PARTY_SITE_ID = IND_HCAS1.PARTY_SITE_ID
AND IND_HCP.COLLECTOR_ID = IND_RC.COLLECTOR_ID
AND IND_HCP.CUST_ACCOUNT_ID = IND_HCA.CUST_ACCOUNT_ID
AND IND_HCP.SITE_USE_ID = IND_HCSU.SITE_USE_ID
AND IND_OTT.TRANSACTION_TYPE_ID = IND_OH.ORDER_TYPE_ID
AND IND_OTT.NAME IN ('D0M RC Imported Sale','D0M RC Indigenous Sale')
GROUP BY
US_CUST.CTA_TRX_NUMBER,
US_CUST.CTA_TRX__DATE ,
US_CUST.DUE_DATE ,
US_CUST.DAYS_LATE ,
US_CUST.GL_DATE,
US_CUST.AGING ,
US_CUST.ORIGINAL_AMOUNT,
US_CUST.APPLIED_AMOUNT ,
US_CUST.REMAIN_AMOUNT,
IND_APS.INVOICE_CURRENCY_CODE,
US_CUST.BILL_TO_CUSTOMER_NO ,
US_CUST.BILL_TO_CUSTOMER_NAME ,
US_CUST.BILL_TO_ADDRESS,
IND_RCTA.CT_REFERENCE,
IND_RCTA.ATTRIBUTE15 ,
IND_OH.CUST_PO_NUMBER,
IND_OH.FREIGHT_CARRIER_CODE,
IND_HCSU.LOCATION,
IND_HCA.ACCOUNT_NUMBER,
IND_HP.PARTY_NAME,
IND_HCSU1.LOCATION,
IND_HL.ADDRESS1 ||' ' || IND_HL.ADDRESS2 ||' '|| IND_HL.ADDRESS3 || ' ' ||IND_HL.PROVINCE||' '|| IND_HL.CITY ||' '|| IND_HL.STATE ||' '|| IND_HL.POSTAL_CODE ||' '||IND_HL.COUNTRY,
IND_RC.NAME,
IND_APS.EXCHANGE_RATE
ORDER BY 1

I have used one custome table ARC.XX_CRC_PRC_REP from US instance. Its query is :

INSERT INTO ARC.XX_CRC_PRC_REP
SELECT /*+ CHOOSE INDEX(APS AR.AR_PAYMENT_SCHEDULES_N2) */
CTA.TRX_NUMBER CTA_TRX_NUMBER,
CTA.TRX_DATE CTA_TRX__DATE,
MIN(APS.DUE_DATE) DUE_DATE,
MAX(TO_DATE('04-NOV-2007','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR')) DAYS_LATE,
DECODE(MAX(SIGN(CEIL(TO_NUMBER(TO_DATE('04-NOV-2007','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR'))/30))),-1,'Curr Bucket',0,'Curr Bucket',DECODE(MAX(CEIL(TO_NUMBER(TO_DATE('04-NOV-2007','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR'))/30)),1,'1-30 Days',2,'31-60 Days',3,'61-90 Days',4,'91-120 Days',5,'121-150 Days',6,'151-180 Days','181 Plus Days')) as AGING,
HCA.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NO,
HP.PARTY_NAME BILL_TO_CUSTOMER_NAME,
HL.ADDRESS1 ||' ' || HL.ADDRESS2 ||' '|| HL.ADDRESS3 || ' ' ||HL.PROVINCE||' '|| HL.CITY ||' '|| HL.STATE ||' '|| HL.POSTAL_CODE ||' '||HL.COUNTRY BILL_TO_ADDRESS,
DECODE(APS.INVOICE_CURRENCY_CODE,'INR',SUM(NVL(APS.AMOUNT_DUE_ORIGINAL,0)),SUM(NVL(APS.AMOUNT_DUE_ORIGINAL,0))*NVL(APS.EXCHANGE_RATE, 1)) ORIGINAL_AMOUNT,
DECODE(APS.INVOICE_CURRENCY_CODE,'INR',SUM(NVL(APS.AMOUNT_APPLIED,0)),SUM(NVL(APS.AMOUNT_APPLIED,0))*NVL(APS.EXCHANGE_RATE,1)) APPLIED_AMOUNT,
--SUM(NVL(APS.AMOUNT_APPLIED,0)) APPLIED_AMOUNT,
ARC.XX_GET_REMNG_AMT(APS.TRX_NUMBER,:USER_ENT_DT,APS.GL_DATE) REMAIN_AMOUNT,
HCASA.ORIG_SYSTEM_REFERENCE HCASA_ORIG_SYSTEM_REFERENCE,
HCASA.ATTRIBUTE10 HCASA_ATTB_10,
HCASA.ATTRIBUTE11 HCASA_ATTB_11,
CTLA.ATTRIBUTE11 CTLA_ATTB_11,
CTLA.ACCOUNT_CLASS,
CTLA.GL_DATE GL_DATE
FROM
AR.RA_CUSTOMER_TRX_ALL CTA,
AR.RA_CUST_TRX_TYPES_ALL CTTA,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL CTLA,
AR.HZ_PARTIES HP,
AR.HZ_CUST_ACCOUNTS HCA,
AR.HZ_CUST_SITE_USES_ALL HCSUA,
AR.HZ_LOCATIONS HL,
AR.HZ_PARTY_SITES HPS,
AR.AR_PAYMENT_SCHEDULES_ALL APS,
AR.HZ_CUST_ACCT_SITES_ALL HCASA
WHERE
CTA.ORG_ID = '22466'
AND CTTA.ORG_ID = '22466'
AND CTLA.ORG_ID = '22466'
AND HCSUA.ORG_ID = '22466'
AND APS.ORG_ID = '22466'
AND HCASA.ORG_ID = '22466'
AND CTLA.GL_DATE BETWEEN '01-OCT-2007' AND '31-OCT-2007'
AND CTLA.ACCOUNT_CLASS = 'REC'
AND CTA.CUST_TRX_TYPE_ID = CTTA.CUST_TRX_TYPE_ID
AND CTLA.CUSTOMER_TRX_ID = CTA.CUSTOMER_TRX_ID
AND HCA.CUST_ACCOUNT_ID = CTA.BILL_TO_CUSTOMER_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HCSUA.SITE_USE_ID = CTA.BILL_TO_SITE_USE_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_ID = HCA.PARTY_ID
AND HPS.ORIG_SYSTEM_REFERENCE = HCSUA.ORIG_SYSTEM_REFERENCE
AND APS.CUSTOMER_TRX_ID = CTLA.CUSTOMER_TRX_ID
AND HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
GROUP BY
CTA.TRX_NUMBER,
CTA.TRX_DATE,
HCA.ACCOUNT_NUMBER ,
HP.PARTY_NAME ,
HL.ADDRESS1 ||' ' || HL.ADDRESS2 ||' '|| HL.ADDRESS3 || ' ' ||HL.PROVINCE||' '|| HL.CITY ||' '|| HL.STATE ||' '|| HL.POSTAL_CODE ||' '||HL.COUNTRY ,
APS.INVOICE_CURRENCY_CODE,
HCASA.ORIG_SYSTEM_REFERENCE,
HCASA.ATTRIBUTE10,
HCASA.ATTRIBUTE11,
CTLA.ATTRIBUTE11,
CTLA.ACCOUNT_CLASS,
CTLA.GL_DATE,
APS.TRX_NUMBER,
APS.GL_DATE,
APS.EXCHANGE_RATE
ORDER BY CTA.TRX_NUMBER

Now I attached that US instance tables data with Indian instance data through the dblink. All dblink tables are coming from Indian Instance.
Re: Update Query [message #297584 is a reply to message #297473] Fri, 01 February 2008 08:42 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Please format your posts with CODE tags. This is unreadable to the point it may cause vertigo in some people.
Previous Topic: first five rows and last five rows
Next Topic: Passing Parameters into a Stored Procedure?
Goto Forum:
  


Current Time: Thu Feb 13 23:24:43 CST 2025