| Update Query [message #650589] |
Thu, 28 April 2016 02:07  |
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
|
Senior Member |
|
|
Hi,
I am facing an issue in the update query. The below query is from SQL Server and I have modified it to Oracle standard. The table structure is given below. The M_PRICES table is having nearly 4 million records and the CURRCONVERTER table is having 25 records.
Request your help in making this query workable. When I ran this query it goes on running and finally came out with the exception as below.
SQL Error: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
30036. 00000 - "unable to extend segment by %s in undo tablespace '%s'"
*Cause: the specified undo tablespace has no more space available.
*Action: Add more space to the undo tablespace before retrying
the operation. An alternative is to wait until active
transactions to commit.
SQL Server Query:
update [m_prices] set [m_prices].currencycode = [pricesurfer_background]..CurrConverter.IsoCode from [pricesurfer_background]..CurrConverter where [m_prices].currencycode = [pricesurfer_background]..CurrConverter.currencycode
Modified Oracle Query
update m_prices m
set m.currencycode = (select b.isocode from pricesurfer_background.currconverter b
where m.currencycode = b.currencycode)
where exists (select 1
from pricesurfer_background.currconverter b
where m.currencycode = b.currencycode)
Table Structure:
M_PRICES Table Structure
COLUMN_NAME DATA_TYPE NULLABLE
PRODUCTID NUMBER No
COUNTRYCODE VARCHAR2(2 BYTE) No
CURRENCYCODE VARCHAR2(3 BYTE) No
INCOTERM VARCHAR2(3 BYTE) No
PRICECUR NUMBER(23,5) Yes
OLDPRICECUR NUMBER(23,5) Yes
STARTEFFECTIVEDATE DATE Yes
REFPRICE NUMBER(23,5) Yes
QUANTITYBREAKSTPT NUMBER(5,0) Yes
SUPPORTBUNDLEFLAG NUMBER(2,0) No
FILEID NUMBER(38,0) Yes
CURRCONVERTER Table Structure
COLUMN_NAME DATA_TYPE NULLABLE
CURRENCYCODE VARCHAR2(2 BYTE) No
ISOCODE VARCHAR2(3 BYTE) No
Sample data available in the tables.
CURRENCYCODE ISOCODE
BC BRL
TD CAD
SF CHF
TB THB
PRODUCTID COUNTRYCODE CURRENCYCODE INCOTERM PRICECUR
389581 TH TB DP NULL
389581 TW TD DP NULL
389651 BE EC DP NULL
Kindly help me.
Thanks
Regards
pstanand.
|
|
|
|
| Re: Update Query [message #650590 is a reply to message #650589] |
Thu, 28 April 2016 02:26  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
You are running out of undo, it is not a query structure problem (technically).
Ask your dba what size the undo tablespace is and if there are other things running at the same time as yours.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Update Query [message #650641 is a reply to message #650639] |
Thu, 28 April 2016 10:21 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
I have added an index on a table that had 3 rows and the application using the table ran twice as fast without any code changes. I would change the table from a standard table to an ORGANIZATION INDEX table. That is what I use for any reference table
[Updated on: Thu, 28 April 2016 10:21] Report message to a moderator |
|
|
|
| Re: Update Query [message #650644 is a reply to message #650641] |
Thu, 28 April 2016 10:49 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I was thinking about the sub-query in the SET. That would have to go to the table to get isocode and so using the index would be pointless overhead. Having both columns on the index may help.
For the sub-query in the where an index on currencycode alone may improve performance as it doesn't need to hit the table at all then.
All this assumes performance is actually a problem. Running out of undo is only vaguely related to performance.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
