| Unable to update newly added column in existing table [message #585050] |
Tue, 21 May 2013 09:51  |
 |
s.m.ramachandran
Messages: 20
Registered: September 2012
|
Junior Member |
|
|
Hi,
I am facing some challenge while running update query on newly added column in existing table.
Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1
No of records in table : 12572770
Below are the step i followed.
1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.
SQL> desc testtablename
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUMNNAME1 VARCHAR2(8)
COLUMNNAME2 CHAR(1)
COLUMNNAME3 CHAR(1)
COLUMNNAME4 VARCHAR2(8)
COLUMNNAME5 VARCHAR2(11)
COLUMNNAME6 NUMBER(9)
COLUMNNAME7 VARCHAR2(10)
COLUMNNAME8 VARCHAR2(10)
COLUMNNAME9 VARCHAR2(9)
COLUMNNAME10 VARCHAR2(6)
COLUMNNAME11 VARCHAR2(80)
COLUMNNAME12 VARCHAR2(80)
COLUMNNAME13 VARCHAR2(80)
COLUMNNAME14 LONG
COLUMNNAME15 NUMBER(5)
COLUMNNAME16 VARCHAR2(2)
COLUMNNAME17 VARCHAR2(15)
COLUMNNAME18 VARCHAR2(15)
COLUMNNAME19 DATE
COLUMNNAME20 NUMBER(5)
COLUMNNAME21 VARCHAR2(15)
COLUMNNAME22 VARCHAR2(15)
COLUMNNAME23 DATE
COLUMNNAME24 DATE
COLUMNNAME25 VARCHAR2(2)
COLUMNNAME26 NUMBER(5)
COLUMNNAME27 DATE
COLUMNNAME28 VARCHAR2(8 CHAR)
SQL> Alter table testtablename add ( COLUMNNAME29 varchar2(8));
Table altered.
SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;
INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME 1 COLUMNNAME24
Problem faced & My analysis
1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.
Kindly let me know what is the way forward to identify the issue.
Below are the queries completed successfully
Update Testtablename
Set Columnname29 = '1'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname1 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below are the queries hanging in database
Update Testtablename
Set Columnname29 = Columnname1
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname29 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below is character set in database
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MM-YYYY
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET US7ASCII
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Please let me know for further inputs/clarification if required.
Thanks & Regards
Ramachandran S M
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Unable to update newly added column in existing table [message #586067 is a reply to message #585118] |
Mon, 03 June 2013 04:36  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
s.m.ramachandran wrote on Wed, 22 May 2013 16:12
I have identified the issue, while executing update query on newly added column row migration is happening. i.e. chained_rows
I wonder how it is chained row issue? With just 29 columns and all columns except (COLUMNNAME14 LONG) have size not more than 80 bytes.
Can you provide the output of the following query:-
1. SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0 and table_name='testtablename';
2. ANALYZE TABLE tablename LIST CHAINED ROWS;
3. SELECT * FROM chained_rows;
I guess, COLUMNNAME14 should be the culprit.
|
|
|
|
| Re: Unable to update newly added column in existing table [message #586117 is a reply to message #586067] |
Mon, 03 June 2013 12:52 |
|
s.m.ramachandran
Messages: 20
Registered: September 2012
|
Junior Member |
|
|
Hi,
The environment is no more, so i could not get the output.
Quote:Can you provide the output of the following query:-
1. SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0 and table_name='testtablename';
2. ANALYZE TABLE tablename LIST CHAINED ROWS;
3. SELECT * FROM chained_rows;
Yes, COLUMNNAME14 caused the issue.
Quote:I guess, COLUMNNAME14 should be the culprit.
Also I have identified chained rows by executing ANALYZE LIST for the table.
Thanks & Regards
Ramachandran S M
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
