Big Challenge, need to convert a field from number to char Options

[nomadetech]

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

[burleson]

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

[nomadetech]

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

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

[burleson]

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));

[nomadetech]

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

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));

[nomadetech]

Still I don't understand why a number field cannot be converted to varchar2, since numeric fits in varchar2 type....

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

[tabreaz]

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?

[nomadetech]

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

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?

[tabreaz]

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

With update, I'm just worried about row chaining

[nomadetech]

I'm not following you, I need to populate this table and freeze production.
P.

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

With update, I'm just worried about row chaining

[Asad]

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

[nomadetech]

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

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

[Asad]

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

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

[herod]

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