Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mr.

Asked: October 18, 2016 - 5:01 am UTC

Last updated: October 19, 2016 - 12:54 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Dear Team,

Kindly help me for below problems
1) Needs to update all the records of 1 billion record table. Only one column values to be updated to reverse the order (abcde -> edcba)
2) Needs to update records based on a column condition (column not having index) of a 1 billion record table. Update the column to reverse the order if the no indexed column is having a particular value. Let me know for 2 cases a) 1% records to be updated b) 20% records to be updated

-
baig

and Connor said...

1)

If space permits, create-table-as-select (CTAS) will probably be the fastest means of achieving this. Parallel create and parallel select, with nologging (if appropriate) can make this very quick.

If not enough space for a copy of the table, then you'd potentially look at parallel dml or dbms_parallel_execute. But you'll burn a lot of undo/redo space obviously.

2)

Same as 1, but there's a sliding scale of when its not practical to do a CTAS based on the percentage of rows being altered. What that percentage is, depends on each platform, eg, cpu capacity, storage speed etc. There would be a strong *probability* that 1% leans towards an update, and 20% might lean toward a CTAS. But those thresholds vary from system to system, platform to platform. In a real production situation, you'd do some benchmarking to make an informed decision and work from that.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Mr Baig, October 18, 2016 - 6:55 am UTC

Really appreciate your quick response. Hats off...
In our application there currently there is not parallel executions.
Do we need check some parameters to enable or it will work for all?
If we do direct update but the updated column is not an index column then indexes on the table would be updated?
Chris Saxon
October 18, 2016 - 1:32 pm UTC

Read the following for more details on parallel DML:

https://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm

Oracle only updates an index if the indexed values change. So if you do an update and there's no index on the target column, then no indexes are modified.

Is the virtual col approach reliable to meet business?

Ghassan, October 18, 2016 - 11:04 am UTC

In addition to the answer above that is very good.
Conseder to add virtual column as the reverse say col2 of that say col1

You could also update select with nologging col1= col2 and drop the col2 after dml.

You could also export and import with remap
You could also merge using the rowid hint (with parallel nologging )

You could also use the dbms redefinition. ..

Lot of solutions for a simple task
Chris Saxon
October 18, 2016 - 1:35 pm UTC

A virtual column is an interesting idea. It could get tricky to maintain if they need to update the original column.

index

A reader, October 18, 2016 - 8:17 pm UTC

Any reason you can't index the unindexed column? Even temporarily?
Connor McDonald
October 19, 2016 - 12:54 am UTC

You could - but to build that index, means a scan of the whole table...and if you are doing that, you may as well do the work there and then

More to Explore

DBMS_PARALLEL_EXECUTE

More on PL/SQL routine DBMS_PARALLEL_EXECUTE here