|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: online table structure changes without affecting the user [message #660802 is a reply to message #660801] |
Mon, 27 February 2017 08:22   |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
What exactly do you mean by "backfill the data from source". Are you adding a new column with a default value? are you literately using another table to populate this new table? Duplicated data is not a good idea when you can easily join the tables if you need to display that other column. What is your database version. Oracle 12c has a lot of new features to do things like this.
[Updated on: Mon, 27 February 2017 08:23] Report message to a moderator |
|
|
|
|
|
|
|
| Re: online table structure changes without affecting the user [message #660805 is a reply to message #660801] |
Mon, 27 February 2017 08:27 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
cookiemonster wrote on Mon, 27 February 2017 14:18It'll be fully accessible for queries, but the update will lock all rows.
There's no real way round that though. Assuming that the table has a primary key one could create a second table with just the key column and the new column, and insert the billion rows into it with the "backfilled" values. Then rename the original table, cover it and the new table with view that has appropriate INSTEAD OF triggers and there you are: column added and populated, zero downtime, no row locks.
[Updated on: Mon, 27 February 2017 08:30] Report message to a moderator |
|
|
|
|
|
| Re: online table structure changes without affecting the user [message #660809 is a reply to message #660806] |
Mon, 27 February 2017 08:36 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
While I normally would always use something like
update my_table a
set new_column =
(select load_value
from work_table b
where a.id_column = b.id_column);
in your billion row table it would lock every single row.
However while MUCH slower the following one will keep your table open and usable.
DECLARE
Cnt NUMBER;
BEGIN
Cnt := 0;
FOR Pnt IN (SELECT Load_value, Id_column
FROM Work_table)
LOOP
UPDATE My_table A
SET A.New_column = Pnt.Load_value
WHERE A.Id_column = Pnt.Id_column;
Cnt := Cnt + 1;
IF MOD (Cnt, 100) = 0
THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
There might be other options if we knew your database version. This script will work with any of the versions.
[Updated on: Mon, 27 February 2017 08:37] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: online table structure changes without affecting the user [message #660813 is a reply to message #660811] |
Mon, 27 February 2017 08:44 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
cookiemonster wrote on Mon, 27 February 2017 09:41Trouble with the slow approach is presumably there's going to be code that queries the new column as soon as it's added. So having an approach where queries can see that column as null when it's not supposed to be could cause all sorts of interesting bugs.
The code assumes that code using the new column will not be implemented until the update is finished but better then having your main transaction table being locked for a couple of hours. But that's why I am not a fan of this method, but sometimes you gotta do what you gotta do.
[Updated on: Mon, 27 February 2017 08:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: online table structure changes without affecting the user [message #660818 is a reply to message #660815] |
Mon, 27 February 2017 08:53 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I wouldn't want to split a table into 2 to avoid short term pain.
You could
1) Rename table
2) Create view with tables original name and columns
3) Add new column to table
4) Add before insert trigger to populate new column with default value
5) Update existing rows in small chunks
6) Drop view
7) Rename table back to original name.
|
|
|
|
| Re: online table structure changes without affecting the user [message #660820 is a reply to message #660818] |
Mon, 27 February 2017 09:02 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
Actually starting in 11g adding a column with a default value takes less then a second. The database just sets up an nvl behind the scene to add the default value to the new column. Works very nice. So no trigger would be needed. Any of these solutions are predicated on the table having no users for the short time needed to get the column into the table. Also renaming the table would invalidate any code in the database that references the table until you can get the view in place and then recompile all the invalid code. Also difficult on a busy production table.
|
|
|
|
| Re: online table structure changes without affecting the user [message #660885 is a reply to message #660803] |
Tue, 28 February 2017 06:49 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
manikandan23 wrote on Mon, 27 February 2017 08:26Hi, Thanks for the responses.
Backfilling the data means, Source is sending a new field with values and this has to be added a new column and the corresponding values from the source.
Sounds like you intend for this to be a repeating, "on-the-fly" process. If so, it is a very bad implementation of some unknown (to us) business problem.
Perhaps it's time (past time, actually) to step back and look at the business problem, laying aside any pre-conceived notions of how to solve it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
