Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Big Challenge, need to convert a field from number to char
nomadetech
post Feb 8 2007, 05:20 AM
Post #1


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



Hello,
The table is 1 terabytes in size. I need to convert one of the field from number to varchar2.
The only way I can do this is:
1) add the new field with a temporary name;
2) update the new field with the current values of the old field;
3) set unused column on the old field and drop the column
4) rename the new field to the old field's name;

My problem is 2), updating the column, it takes 3 hours just for 2 days of data.... I have 13 months.

Am I missing something? Is there a faster way to do this?

Thanks Pierre
Go to the top of the page
 
+Quote Post
burleson
post Feb 8 2007, 05:52 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,544
Joined: 26-January 04
Member No.: 13



Hi,

>> 2), updating the column, it takes 3 hours just for 2 days of data

This is a standard SQL update like " update tab1 set charfirld = to_char(numfield);"

If so, consider:

Parallel DML (only if you have multiple CPU's on your server):

http://www.dba-oracle.com/t_parallel_dml_updates_inserts.htm

Re-write in PL/SQL with bulking (FORALL and bulk collect):

http://www.dba-oracle.com/plsql/t_plsql_bulk_update.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
nomadetech
post Feb 8 2007, 07:22 AM
Post #3


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



Thanks, I wil try to apply to bulk update, but I fear I will run out of memory if I do this, I have about 1 terabytes.
Pierre
QUOTE (burleson @ Feb 8 2007, 11:53 AM) *
Hi,

>> 2), updating the column, it takes 3 hours just for 2 days of data

This is a standard SQL update like " update tab1 set charfirld = to_char(numfield);"

If so, consider:

Parallel DML (only if you have multiple CPU's on your server):

http://www.dba-oracle.com/t_parallel_dml_updates_inserts.htm

Re-write in PL/SQL with bulking (FORALL and bulk collect):

http://www.dba-oracle.com/plsql/t_plsql_bulk_update.htm
Go to the top of the page
 
+Quote Post
burleson
post Feb 8 2007, 07:34 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,544
Joined: 26-January 04
Member No.: 13



HI,

>> I fear I will run out of memory if I do this, I have about 1 terabytes.

Yeah, you will probably not have enough UNDO to do the whole update at once, so you will want to choose a COMMIT frequency . . . .

Oh, could you maybe use a function-based index on the numeric column, so you don;t need a CHAR equivelant?

create index xxx on tab (to_char(numcol));


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
nomadetech
post Feb 8 2007, 08:20 AM
Post #5


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



Thanks for your feedback, the function based index is not a bad idea, but they need to put alpha-numeric in the field from now on, so if it remains (number) inserts will fail.
Pierre
QUOTE (burleson @ Feb 8 2007, 01:35 PM) *
HI,

>> I fear I will run out of memory if I do this, I have about 1 terabytes.

Yeah, you will probably not have enough UNDO to do the whole update at once, so you will want to choose a COMMIT frequency . . . .

Oh, could you maybe use a function-based index on the numeric column, so you don;t need a CHAR equivelant?

create index xxx on tab (to_char(numcol));
Go to the top of the page
 
+Quote Post
nomadetech
post Feb 8 2007, 08:37 AM
Post #6


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



Still I don't understand why a number field cannot be converted to varchar2, since numeric fits in varchar2 type....
QUOTE (nomadetech @ Feb 8 2007, 02:21 PM) *
Thanks for your feedback, the function based index is not a bad idea, but they need to put alpha-numeric in the field from now on, so if it remains (number) inserts will fail.
Pierre
Go to the top of the page
 
+Quote Post
tabreaz
post Feb 8 2007, 08:53 AM
Post #7


Advanced Member
***

Group: Members
Posts: 105
Joined: 18-December 06
Member No.: 6,199



Did you try to run "ALTER TABLE" statement.

Presently I dont have access to oracle, but give a try. May be Its possible to convert numeric data to Char.

But regarding adding a new column and droping old column may cause row chaining, think about CTAS statement, with proper partition and parallel query.

Is present table uses partitions?
Go to the top of the page
 
+Quote Post
nomadetech
post Feb 8 2007, 09:30 AM
Post #8


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



mind you , it was the first command I tried 'alter table...' logically it should be permitted but it isn't.
the table has daily partitions (12 million rows per day), I could run multiple update on partition I know that, but they want a short outage, that is impossible. 2 days of data takes 3 hours roughly. there are 390 to convert. I have a workaround which would be to rename the old field, create the new field with the old name, and have a decode put in their query to go to the right field when one or the other is null.
Pierre
QUOTE (tabreaz @ Feb 8 2007, 02:54 PM) *
Did you try to run "ALTER TABLE" statement.

Presently I dont have access to oracle, but give a try. May be Its possible to convert numeric data to Char.

But regarding adding a new column and droping old column may cause row chaining, think about CTAS statement, with proper partition and parallel query.

Is present table uses partitions?
Go to the top of the page
 
+Quote Post
tabreaz
post Feb 8 2007, 09:36 AM
Post #9


Advanced Member
***

Group: Members
Posts: 105
Joined: 18-December 06
Member No.: 6,199



How about Creating another table, there will be no downtime?

With update, I'm just worried about row chaining
Go to the top of the page
 
+Quote Post
nomadetech
post Feb 8 2007, 09:49 AM
Post #10


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



I'm not following you, I need to populate this table and freeze production.
P.
QUOTE (tabreaz @ Feb 8 2007, 03:37 PM) *
How about Creating another table, there will be no downtime?

With update, I'm just worried about row chaining
Go to the top of the page
 
+Quote Post
Asad
post Feb 8 2007, 10:01 AM
Post #11


Advanced Member
***

Group: Members
Posts: 392
Joined: 30-November 05
From: AZ
Member No.: 3,343



hi,

creating another table does seem smart advise... there is something like this on tom kyte's notes. do letus know if follwing link helps you.

http://asktom.oracle.com/Please/asktom/f?p...D:6407993912330
Go to the top of the page
 
+Quote Post
nomadetech
post Feb 8 2007, 10:20 AM
Post #12


Advanced Member
***

Group: Members
Posts: 43
Joined: 2-October 06
Member No.: 5,201



Thanks, I thought about this, but I need a solution that runs under 24 hours, I don't it's possible, there are 1 billion records to recreate. + the 16 indexes.
Thanks.
Pierre
QUOTE (Asad @ Feb 8 2007, 04:02 PM) *
hi,

creating another table does seem smart advise... there is something like this on tom kyte's notes. do letus know if follwing link helps you.

http://asktom.oracle.com/Please/asktom/f?p...D:6407993912330
Go to the top of the page
 
+Quote Post
Asad
post Feb 8 2007, 10:45 AM
Post #13


Advanced Member
***

Group: Members
Posts: 392
Joined: 30-November 05
From: AZ
Member No.: 3,343



how about partitionwise table creation in parallel - you can later merge those table in partition. would this help?

QUOTE (nomadetech @ Feb 8 2007, 03:21 PM) *
Thanks, I thought about this, but I need a solution that runs under 24 hours, I don't it's possible, there are 1 billion records to recreate. + the 16 indexes.
Thanks.
Pierre
Go to the top of the page
 
+Quote Post
herod
post Feb 9 2007, 05:15 PM
Post #14


Advanced Member
***

Group: Members
Posts: 54
Joined: 20-July 06
Member No.: 4,611



I don't understand why the update takes so long. I just tried the following script on my laptop, 5 million rows, not a billion, BUT I assume your server is a little more powerful than my laptop. Drop any indexes on those columns. Make sure your undo retention is set very low or off. Run the updates and the alters, recreate the indexes.


Do it all in parallel if you can.

CODE
SQL>
SQL> DROP TABLE MILLIONROWS;

Table dropped.

Elapsed: 00:00:00.50
SQL>
SQL> CREATE
  2  TABLE
  3  MILLIONROWS
  4  (
  5  CHARTYPE VARCHAR2(20)
  6  ,NUMBERTYPE NUMBER
  7  );

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> INSERT INTO MILLIONROWS SELECT ROUND(TO_CHAR(DBMS_RANDOM.VALUE(1,10)),3),DBMS_RANDOM.VALUE(1,10) FROM DUAL CONNECT BY LEVEL <= 5000000;

5000000 rows created.

Elapsed: 00:01:59.18
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> LOCK TABLE MILLIONROWS IN EXCLUSIVE MODE;

Table(s) Locked.

Elapsed: 00:00:00.00
SQL>
SQL> ALTER TABLE MILLIONROWS
  2   ADD (
  3    TEMPCOL NUMBER
  4   )
  5  /

Table altered.

Elapsed: 00:00:00.09
SQL>
SQL>
SQL> UPDATE MILLIONROWS SET TEMPCOL=TO_NUMBER(CHARTYPE),CHARTYPE=NULL;

5000000 rows updated.

Elapsed: 00:02:37.03
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> LOCK TABLE MILLIONROWS IN EXCLUSIVE MODE;

Table(s) Locked.

Elapsed: 00:00:00.00
SQL>
SQL> ALTER TABLE MILLIONROWS
  2   MODIFY (
  3    CHARTYPE NUMBER (20)
  4  
SQL>  )
SP2-0042: unknown command ")" - rest of line ignored.
SQL> /
  CHARTYPE NUMBER (20)
                     *
ERROR at line 3:
ORA-00907: missing right parenthesis


Elapsed: 00:00:00.00
SQL>
SQL> UPDATE MILLIONROWS SET CHARTYPE=TEMPCOL,TEMPCOL=NULL;

5000000 rows updated.

Elapsed: 00:03:01.87
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> LOCK TABLE MILLIONROWS IN EXCLUSIVE MODE;

Table(s) Locked.

Elapsed: 00:00:00.00
SQL> ALTER TABLE MILLIONROWS
  2   DROP (
  3    TEMPCOL
  4   )
  5  /

Table altered.

Elapsed: 00:00:21.40
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
SQL> EXIT


--------------------
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 1st October 2014 - 09:18 PM