0

Oracle 12C, non partitioned, no ASM.

This is the background. I have a table with multiple columns, 3 of them being -

TRAN_DATE                             DATE
TRAN_TIME                             TIMESTAMP(6)
FINAL_DATETIME                        NOT NULL TIMESTAMP(6)

The table has around 70 million records. What I want to do is concatenate the tran_date and the tran_time field and update the final_datetime field with that output, for all 70 million records.

This is the query I have -

update MYSCHEMA.MYTAB set FINAL_DATETIME = (to_timestamp( (to_char(tran_date, 'YYYY/MM/DD') || ' ' ||to_char(TRAN_TIME,'HH24MISS') ), 'YYYY-MM-DD HH24:MI:SS.FF'))

Eg:

At present (for one record)

TRAN_DATE=01-DEC-16
TRAN_TIME=01-JAN-70 12.35.58.000000 AM    /*I need only the time part from this*/
FINAL_DATETIME=22-OCT-18 04.37.18.870000 PM

Post the query - the FINAL_DATETIME needs to be

01-DEC-16 12.35.58.000000 AM

The to_timestamp does require 2 character strings and I fear this will slow down the update a lot. Any suggestions?

What more can I do to increase performance? No one else will be using the table at this point, so, I do have the option to

  • Drop indices

  • Turn off logging

    and anything more anyone can suggest.

Any help is appreciated.

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
Sandeep Menon
  • 1
  • Instead of running a single update statement on 70 million records and committing only at the end, you may want to batch your updates and commit after each batch. – ArtBajji Oct 27 '18 at 15:05
  • This is an option we are exploring, just worried that automating it might still require some level of oversight since it'll take time to complete. – Sandeep Menon Oct 29 '18 at 02:59

1 Answers1

1

I would prefer CTAS method and your job would be simpler if you didn't have indexes, triggers and constraints on your table.

Create a new table for the column to be modified.

CREATE TABLE mytab_new
NOLOGGING
     AS
      SELECT /*+ FULL(mytab) PARALLEL(mytab, 10) */ --hint to speed up the select.
       CAST(tran_date AS TIMESTAMP) + ( tran_time - trunc(tran_time) ) AS final_datetime
          FROM mytab;

I have included only one(the final) column in your new table because storing the other two in the new table is waste of resources. You may include other columns in select apart from the two now redundant ones.

Read logging/nologging to know about NOLOGGING option in the select.

Next step is to rebuild indexes, triggers and constraints for the new table new_mytab using the definition from mytab for other columns if they exist.

Then rename the tables

rename mytab     to mytab_bkp;

rename mytab_new to mytab;

You may drop the table mytab_bkp after validating the new table or later when you feel you no longer need it.

Demo

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45