Home » SQL & PL/SQL » SQL & PL/SQL » online table structure changes without affecting the user (11g)
online table structure changes without affecting the user [message #660729] Sat, 25 February 2017 23:20 Go to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Given a billion row table, how can we add a new column and backfill the data from source without impacting the user?
Re: online table structure changes without affecting the user [message #660731 is a reply to message #660729] Sun, 26 February 2017 01:04 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

you can do online by altering the table by adding a new column and populate from source.

As long as you have taken care in the code there is no insert into <another table> select * from <changed table>
which will fail otherwise due to additional column.



regards
garan
Re: online table structure changes without affecting the user [message #660743 is a reply to message #660729] Sun, 26 February 2017 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you mean by "without impacting the user"?

One solution; do it when the users are not there.

Re: online table structure changes without affecting the user [message #660748 is a reply to message #660729] Sun, 26 February 2017 03:05 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Adding a column is trivial, no-one will notice that. Populating it may result in row migration. Potentially, every row will migrate in which case your index efficiency will degrade. You may want to investigate whether this would be a problem.
Re: online table structure changes without affecting the user [message #660763 is a reply to message #660748] Sun, 26 February 2017 17:06 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Thanks guys!

The table cant be down and it is an online transaction table and any other suggestions appreciated.
Re: online table structure changes without affecting the user [message #660765 is a reply to message #660729] Sun, 26 February 2017 18:13 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

as conveyed do it when the users are not there or do it during weekends where business users don't connect

garan
Re: online table structure changes without affecting the user [message #660793 is a reply to message #660763] Mon, 27 February 2017 06:53 Go to previous messageGo to next message
EdStevens
Messages: 975
Registered: September 2013
Senior Member
manikandan23 wrote on Sun, 26 February 2017 17:06
Thanks guys!

The table cant be down and it is an online transaction table and any other suggestions appreciated.
the solution does not take the table off line. It will remain fully accessible, though performance may suffer.
Re: online table structure changes without affecting the user [message #660801 is a reply to message #660793] Mon, 27 February 2017 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'll be fully accessible for queries, but the update will lock all rows.
There's no real way round that though.
Re: online table structure changes without affecting the user [message #660802 is a reply to message #660801] Mon, 27 February 2017 08:22 Go to previous messageGo to next message
Bill B
Messages: 1797
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 #660803 is a reply to message #660802] Mon, 27 February 2017 08:26 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Hi, 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.
Re: online table structure changes without affecting the user [message #660804 is a reply to message #660803] Mon, 27 February 2017 08:27 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what is the source here?
Re: online table structure changes without affecting the user [message #660805 is a reply to message #660801] Mon, 27 February 2017 08:27 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
cookiemonster wrote on Mon, 27 February 2017 14:18
It'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 #660806 is a reply to message #660803] Mon, 27 February 2017 08:27 Go to previous messageGo to next message
Bill B
Messages: 1797
Registered: December 2004
Senior Member
and again, what version of the database are you using?
Re: online table structure changes without affecting the user [message #660809 is a reply to message #660806] Mon, 27 February 2017 08:36 Go to previous messageGo to next message
Bill B
Messages: 1797
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 #660810 is a reply to message #660809] Mon, 27 February 2017 08:41 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
I am using 11g
Re: online table structure changes without affecting the user [message #660811 is a reply to message #660809] Mon, 27 February 2017 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
Trouble 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.
Re: online table structure changes without affecting the user [message #660812 is a reply to message #660810] Mon, 27 February 2017 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
11g what?
There are lots of 11g versions and not all new features were added to the first one.
Re: online table structure changes without affecting the user [message #660813 is a reply to message #660811] Mon, 27 February 2017 08:44 Go to previous messageGo to next message
Bill B
Messages: 1797
Registered: December 2004
Senior Member
cookiemonster wrote on Mon, 27 February 2017 09:41
Trouble 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 #660814 is a reply to message #660812] Mon, 27 February 2017 08:45 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
11.2.0.2.0
Re: online table structure changes without affecting the user [message #660815 is a reply to message #660813] Mon, 27 February 2017 08:47 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Could one use editions to keep the column effectively invisible (which in release 12.x, would be simple) until fully populated?

I still prefer my solution, though: it is the only one that avoids both locking and row migration.
Re: online table structure changes without affecting the user [message #660818 is a reply to message #660815] Mon, 27 February 2017 08:53 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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 Go to previous messageGo to next message
Bill B
Messages: 1797
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 Go to previous message
EdStevens
Messages: 975
Registered: September 2013
Senior Member
manikandan23 wrote on Mon, 27 February 2017 08:26
Hi, 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.
Previous Topic: Execute Immediate with Dynamic From table
Next Topic: Complex date differences - Bet you can't figure this one out.
Goto Forum:
  


Current Time: Mon Oct 15 13:58:26 CDT 2018