Home » RDBMS Server » Performance Tuning » about update perform
about update perform [message #296574] Mon, 28 January 2008 05:29 Go to next message
zxx2403
Messages: 12
Registered: November 2006
Location: china
Junior Member

DateBase oracle 9.12

----------------------------------------------------------------
I have three table
ITRN_HIS the count : 2210755
C_SOQID the count : 10876
C_SOQMD the count : 158

---------------------------------------------------------------

The update Sql as follow:

-----------------------------------------------------------------
UPDATE ITRN_HIS
SET USER_ALPHA3 = SUBSTR(RPAD(USER_ALPHA3, 15, ' '), 1, 15) || 'Y' || SUBSTR(USER_ALPHA3, 17)
WHERE SOURCE = 5
AND SOURCE_QUAL = 5
AND CCN = 'PASDL'
AND MAS_LOC = 'PRO'
AND USER_ALPHA3 <> ' '
AND NVL(SUBSTR(USER_ALPHA3, 16, 1), ' ') <> 'Y'
AND
(POSTING_DATE BETWEEN TO_DATE('20080101', 'YYYYMMDD') AND TO_DATE('20080131', 'YYYYMMDD'))
AND
USER_ALPHA3 IN
(SELECT SUBSTR(C_SOQMD_INVOICE, 1, 15) FROM C_SOQMD WHERE TRIM(C_SOQMD_INVOICE) <> 'LAST')
AND EXISTS (SELECT *
FROM C_SOQID
WHERE INVOICE = SUBSTR(ITRN_HIS.USER_ALPHA3, 1, 15)
AND SO = ITRN_HIS.REF
AND SO_LINE = ITRN_HIS.REF_LINE)
---------------------------------------------------------------

The sql execute is very slow

What can I do.

Thank you very much
Re: about update perform [message #296579 is a reply to message #296574] Mon, 28 January 2008 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What can I do.

Optimize it.

please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Provide the requested information.

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel

Re: about update perform [message #296586 is a reply to message #296574] Mon, 28 January 2008 06:01 Go to previous messageGo to next message
zxx2403
Messages: 12
Registered: November 2006
Location: china
Junior Member

zxx2403 wrote on Mon, 28 January 2008 05:29
DateBase oracle 9.12

----------------------------------------------------------------
I have three table
ITRN_HIS the count : 2210755
C_SOQID the count : 10876
C_SOQMD the count : 158

---------------------------------------------------------------

The update Sql as follow:

UPDATE ITRN_HIS
SET    USER_ALPHA3 = SUBSTR(RPAD(USER_ALPHA3, 15, ' '), 1, 15) || 'Y' || SUBSTR(USER_ALPHA3, 17)
WHERE  SOURCE = 5
       AND SOURCE_QUAL = 5
       AND CCN = 'PASDL'
       AND MAS_LOC = 'PRO'
       AND USER_ALPHA3 <> ' '
       AND NVL(SUBSTR(USER_ALPHA3, 16, 1), ' ') <> 'Y'
       AND
       (POSTING_DATE BETWEEN TO_DATE('20080101', 'YYYYMMDD') AND TO_DATE('20080131', 'YYYYMMDD'))
       AND
       USER_ALPHA3 IN
       (SELECT SUBSTR(C_SOQMD_INVOICE, 1, 15) FROM C_SOQMD WHERE TRIM(C_SOQMD_INVOICE) <> 'LAST')
       AND EXISTS (SELECT *
        FROM   C_SOQID
        WHERE  INVOICE = SUBSTR(ITRN_HIS.USER_ALPHA3, 1, 15)
               AND SO = ITRN_HIS.REF
               AND SO_LINE = ITRN_HIS.REF_LINE)


The sql execute is very slow

What can I do.

Thank you very much


Re: about update perform [message #296730 is a reply to message #296586] Mon, 28 January 2008 19:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post an Explain Plan and - if possible - TKProf output.
Previous Topic: Measurements
Next Topic: Query is running long
Goto Forum:
  


Current Time: Tue Dec 06 12:39:02 CST 2016

Total time taken to generate the page: 0.12818 seconds