1

I have a requirement where I have to update data type of a column (col_1) from number (38,0) to Varchar2(64). This column is also a part of primary key which is composite.

I am achieving this objective by

1) Adding a new column col_2 of data type varchar2(64)

2) populating data for col_2 with existing column col_1 [UPDATE TABLE_1 SET col_2 = col_1]

3) dropping primary key (this drops index created on pkey)

4) dropping column col_1

5) renaming the new column to col_1 [ALTER TABLE TABLE_1 COLUMN col_2 to col_1]

6) creating primary key (this creates index on pkey)

7) Gather stats on Table

After performing above steps SUCCESSFULLY, I am running my regular Informatica job which performs update/insert on the table. That job is taking way longer and not completing. Insert/Update is part of informatica workflow and I can see the no. of rows is being increased in Target statistics under session properties but it is updating approximately 500 rows per hour.

Is there something I am missing as part of my steps. Do I need to explicitly rebuild the index or gather index. I have checked the LAST_ANALYZED date for Index and Tables and both are updated. The Query plan is also looking same as before.

Oracle version - Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Please help in with your suggestions.

Many Thanks

Keerti

Keerti Surana
  • 19
  • 2
  • Welcome to Stackoverflow, Keerti! Can you provide some of your code (what you have so far) or an [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example)? – RobertS supports Monica Cellio Dec 13 '19 at 10:20
  • Please show your update column in the question. Could be that this update contains `WHERE col_1=number` clause, try to use `WHERE col_1=to_char(number)` instead. – krokodilko Dec 13 '19 at 10:21
  • can you show your code? – Kiran Patil Dec 13 '19 at 10:25
  • How are you doing this step `populating data for col_2 with existing column col_1`? How many rows in the table? How many columns? Please remember that query optimisation is **all about the details** of the specific SQL statement. So please read [this answer on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325), then edit your question to include the necessary information. – APC Dec 13 '19 at 12:17
  • There are several ways a simple alter can make a table less efficient. If the new column uses more space, it might cause row migration (rows can't fit in the same block). You can check for that by running `analyze table TBLDM_EQ_PUB_TXN compute statistics;` and then `select chain_cnt from dba_tables where table_name = 'TBLDM_EQ_PUB_TXN';`. Or maybe the table is much larger, check DBA_SEGMENTS. Or maybe something else weird is going on, can you post the contents of `select dbms_metadata.get_ddl('table', 'TBLDM_EQ_PUB_TXN') from dual;`? You might want to `MOVE` the table to optimize it. – Jon Heller Dec 16 '19 at 02:44

0 Answers0