Adding New Column to Large Tables with Default/Initialization

 

Alter Table Add Column with a separate Update or Alter Table Add Column with Default takes a long time for large tables.

Why it takes long?

  • The update (esp. on tables with many millions of rows) is the issue here. When a default is specified, all rows need to be updated.
  • Updating large amount of rows will 1) take a very long time, 2) fill up the undo segments, 3) generates a large amount of redo. This can be a big overhead.

What can be done?

  • See if the column with default can be NOT NULL.
  • Alter Table Add Column with Default Not Null works fast as it is a data dictionary operation only.

Please see below:

—Create a table with 2 million rows

SQL> CREATE TABLE test AS SELECT LEVEL id,DBMS_RANDOM.string (‘x’, DBMS_RANDOM.VALUE (20, 50)) name, SYSDATE – DBMS_RANDOM.VALUE (1, 365) dt FROM DUAL CONNECT BY LEVEL <= 2000000;

Table created.

Approach#1) Alter Table and Separate Update to initialize all rows

SQL> ALTER TABLE test ADD ind_flag VARCHAR2(2);

Table altered.

Elapsed: 00:00:00.14

SQL> UPDATE TEST 2 SET IND_FLAG =’AC’;

2000000 rows updated.

Elapsed: 00:00:30.47

Approach#2) Alter Table with Default

SQL> ALTER TABLE test ADD ind_flag_dflt VARCHAR2(2) DEFAULT ‘AC’;

Table altered.

Elapsed: 00:00:49.40

Approach#3) Alter Table with Default and NOT NULL

SQL> ALTER TABLE test ADD ind_flag_dflt_nn VARCHAR2(2) DEFAULT ‘AC’ NOT NULL;

Table altered.

Elapsed: 00:00:00.07

Approach #3 is instantaneous. But #1 and #2 takes more than half a minute.

  • If the new NOT NULL column can have a DEFAULT value, then it is a data dictionary operation that is instantaneous.
  • Feature available from 11g.

124 thoughts on “Adding New Column to Large Tables with Default/Initialization”

  63. I am often to blogging and i really appreciate your content. The article has really peaks my interest. I am going to bookmark your site and keep checking for new information.

    Reply

  65. Good blog! I truly love how it is simple on my eyes and the data are well written. I’m wondering how I could be notified whenever a new post has been made. I have subscribed to your feed which must do the trick! Have a nice day!

    Reply

  66. What is Renew? Renew is a dietary supplement designed to support blood flow while also aiming to boost testosterone levels and provide an explosive energy drive

    Reply

  70. Having read this I thought it was very informative. I appreciate you taking the time and effort to put this article together. I once again find myself spending way to much time both reading and commenting. But so what, it was still worth it!

    Reply

  71. Having read this I thought it was very informative. I appreciate you taking the time and effort to put this article together. I once again find myself spending way to much time both reading and commenting. But so what, it was still worth it!

    Reply

  76. Hello There. I found your blog using msn. This is an extremely well written article. I will make sure to bookmark it and come back to read more of your useful info. Thanks for the post. I will certainly return.

    Reply

  77. What is Lottery Defeater Software? Lottery Defeater Software is a plug-and-play Lottery Winning Software that is fully automated. Kenneth created the Lottery Defeater software. Every time someone plays the lottery, it increases their odds of winning by around 98.

    Reply

  78. What Is LeanBiome? LeanBiome is a natural weight loss supplement that reverses bacterial imbalance in your gut microbiome with the help of nine science-backed lean bacteria species with Greenselect Phytosome, a caffeine-free green tea extract crafted with patented phytosome technology.

    Reply

  79. Hello there, just became aware of your blog through Google, and found that it’s truly informative. I’m going to watch out for brussels. I will appreciate if you continue this in future. Lots of people will be benefited from your writing. Cheers!

    Reply

  82. What i do not realize is in truth how you are now not really much more smartly-appreciated than you may be now. You’re very intelligent. You know thus significantly in relation to this matter, made me individually believe it from numerous varied angles. Its like women and men are not involved until it¦s one thing to do with Girl gaga! Your personal stuffs excellent. All the time maintain it up!

    Reply

  83. Great post. I was checking constantly this blog and I am impressed! Extremely useful info particularly the last part 🙂 I care for such info a lot. I was seeking this particular info for a long time. Thank you and best of luck.

    Reply

  85. This blog is definitely rather handy since I’m at the moment creating an internet floral website – although I am only starting out therefore it’s really fairly small, nothing like this site. Can link to a few of the posts here as they are quite. Thanks much. Zoey Olsen

    Reply

  86. Hey there! This post could not be written any better! Reading this post reminds me of my old room mate! He always kept chatting about this. I will forward this page to him. Pretty sure he will have a good read. Thanks for sharing!

    Reply

  87. What’s Taking place i’m new to this, I stumbled upon this I have found It positively helpful and it has aided me out loads. I am hoping to contribute & help other users like its helped me. Good job.

    Reply

  88. This is a very good tips especially to those new to blogosphere, brief and accurate information… Thanks for sharing this one. A must read article.

    Reply

  89. I’m really enjoying the design and layout of your blog. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a designer to create your theme? Great work!

    Reply

  92. I just could not go away your web site prior to suggesting that I actually loved the usual information a person supply in your guests? Is gonna be again often in order to inspect new posts

    Reply

  93. It’s in reality a nice and useful piece of information. I’m glad that you just shared this helpful info with us. Please stay us up to date like this. Thank you for sharing.

    Reply

  94. Its like you learn my mind! You appear to grasp so much approximately this, like you wrote the e book in it or something. I believe that you simply could do with a few to pressure the message home a bit, but instead of that, that is great blog. An excellent read. I will certainly be back.

    Reply

  97. Fantastic post however I was wanting to know if you could write a litte more on this subject? I’d be very grateful if you could elaborate a little bit further. Bless you!

    Reply

  98. I feel that is one of the such a lot important info for me. And i am glad studying your article. But want to commentary on few general issues, The web site taste is great, the articles is in reality great : D. Good process, cheers

    Reply

  99. fantastic put up, very informative. I’m wondering why the opposite experts of this sector don’t notice this. You should proceed your writing. I am sure, you’ve a great readers’ base already!

    Reply

  100. Oh my goodness! a tremendous article dude. Thanks Nonetheless I am experiencing situation with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting similar rss downside? Anyone who is aware of kindly respond. Thnkx

    Reply

  101. Great paintings! That is the kind of information that are meant to be shared across the net. Disgrace on Google for not positioning this put up higher! Come on over and discuss with my web site . Thank you =)

    Reply

  102. The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought youd have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention.

    Reply

  103. Hey very nice blog!! Guy .. Excellent .. Amazing .. I will bookmark your web site and take the feeds additionallyKI am satisfied to find a lot of helpful information right here in the put up, we want develop more strategies on this regard, thanks for sharing. . . . . .

    Reply

  106. I think this is one of the most important info for me. And i am glad reading your article. But want to remark on some general things, The web site style is wonderful, the articles is really nice : D. Good job, cheers

    Reply

  107. What i don’t realize is in reality how you are now not actually much more neatly-favored than you might be right now. You’re very intelligent. You already know thus significantly with regards to this subject, produced me for my part imagine it from numerous varied angles. Its like women and men aren’t fascinated except it?¦s something to do with Woman gaga! Your own stuffs outstanding. At all times deal with it up!

    Reply

  108. Wow, amazing weblog layout! How lengthy have you been running a blog for? you make running a blog look easy. The total glance of your web site is fantastic, let alone the content material!

    Reply

  112. Have you ever considered writing an ebook or guest authoring on other sites? I have a blog centered on the same topics you discuss and would really like to have you share some stories/information. I know my subscribers would enjoy your work. If you are even remotely interested, feel free to shoot me an e-mail.

    Reply

  113. Yesterday, while I was at work, my sister stole my apple ipad and tested to see if it can survive a twenty five foot drop, just so she can be a youtube sensation. My apple ipad is now broken and she has 83 views. I know this is completely off topic but I had to share it with someone!

    Reply

  116. of course like your website but you have to check the spelling on several of your posts. A number of them are rife with spelling issues and I in finding it very troublesome to tell the reality nevertheless I’ll definitely come back again.

    Reply

  123. I know this if off topic but I’m looking into starting my own weblog and was curious what all is required to get set up? I’m assuming having a blog like yours would cost a pretty penny? I’m not very web smart so I’m not 100 positive. Any suggestions or advice would be greatly appreciated. Cheers

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *