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.

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

    2. Wonderful website. Plenty of helpful information here. I am sending it to
      some pals ans also sharing in delicious. And of course, thanks on your effort!

      Reply

    2. Awesome issues here. I am very glad to see
      your post. Thanks so much and I’m taking a look ahead to touch you.
      Will you please drop me a e-mail?

      Reply

  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

    1. Hi, Neat post. There is an issue with your
      web site in web explorer, could test this? IE nonetheless is the market chief and a good component of other people will leave out your
      magnificent writing because of this problem.

      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

    3. Good day! I know this is kinda off topic but I was wondering if you knew where I could find a captcha plugin for my comment form?
      I’m using the same blog platform as yours and I’m having difficulty finding one?

      Thanks a lot!

      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

    2. constantly i used to read smaller posts that as well clear their motive,
      and that is also happening with this piece of
      writing which I am reading now.

      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

    2. My coder is trying to convince me to move to .net from PHP.

      I have always disliked the idea because of the costs.
      But he’s tryiong none the less. I’ve been using Movable-type on various websites for about a year
      and am worried about switching to another platform. I have
      heard great things about blogengine.net. Is
      there a way I can transfer all my wordpress content into it?
      Any help would be greatly appreciated!

      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

    1. I am really enjoying the theme/design of your weblog.
      Do you ever run into any browser compatibility issues?
      A couple of my blog audience have complained about my blog not working correctly in Explorer but looks
      great in Firefox. Do you have any ideas to help
      fix this issue?

      Reply

    1. This is a great post, I truly had a great time reading it. Your writing style is very engaging and your opinions are highly important. Keep it up!

      Reply

    1. You really make it appear really easy together
      with your presentation however I in finding this matter to be
      really something which I believe I would never understand.
      It seems too complex and extremely broad for me. I’m taking a look
      ahead in your next publish, I’ll try to get the hold of it!

      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

  126. Hello! This is my 1st comment here so I just wanted to give a quick shout out and say I truly enjoy reading through your blog posts. Can you recommend any other blogs/websites/forums that deal with the same topics? Thanks a ton!

    Reply

  130. Hello! Quick question that’s completely off topic. Do you know how to make your site mobile friendly? My site looks weird when viewing from my iphone. I’m trying to find a theme or plugin that might be able to fix this problem. If you have any suggestions, please share. Thanks!

    Reply

  135. Does your website have a contact page? I’m having a tough time locating it but, I’d like to shoot you an email. I’ve got some recommendations for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it develop over time.

    Reply

  137. I’m really enjoying the theme/design of your site. Do you ever run into any browser compatibility problems? A few of my blog readers have complained about my site not working correctly in Explorer but looks great in Firefox. Do you have any tips to help fix this issue?

    Reply

  148. Somebody necessarily help to make severely posts I might state. This is the very first time I frequented your web page and up to now? I amazed with the analysis you made to create this particular post amazing. Magnificent activity!

    Reply

  153. Oh my goodness! Amazing article dude! Thank you so much, However I am going through difficulties with your RSS.
    I don’t know the reason why I can’t join it. Is there anybody else getting similar RSS issues?
    Anyone who knows the solution can you kindly respond?
    Thanks!!

    Reply

  158. Prioritize heavy compound lifts, but additionally give attention to isolating the muscle
    tissue to stimulate development from all angles.
    Certain, cardio lovers could argue that working or cycling is enough for leg improvement.
    These activities lack the resistance wanted to maximize muscle progress.

    Up to 15 to twenty units per muscle group and week isn’t unusual for skilled individuals to do, however you need to be
    careful not to do an extreme quantity of too quickly, as that will increase your threat of damage.
    A third advantage of the single-leg deadlift is that you could get a good hamstring workout in with a lighter weight, making it a great various for a
    leg workout at house. To pick the most effective hamstring workout routines, you should first have a basic understanding of your hamstring muscles.
    In this text, we’ll evaluate ten of the best hamstring workouts you are able to do in or out of the fitness center.

    You would possibly need to strengthen your hip flexors if they’re continuously aching in these areas after lengthy runs
    and/or exhausting exercises, Coviello says. This might be
    an indication of damage (or pending injury), which could sideline you from your training or that huge race you’ve got developing.
    Your hip flexors are the muscle tissue within the front of your
    hips that join your leg to your pelvis, explains Erica Coviello, C.P.T.,
    licensed personal coach and RRCA-certified run coach.
    There are loads of causes to coach your legs that transcend enhancing your muscle tone.

    It’s a unilateral leg exercise so it’s going to iron out
    muscle imbalances, whereas placing emphasis on the quadriceps
    and glutes, and enhancing your steadiness and coordination. Quick tip, be certain to wrap the resistance
    band tightly around your hands so you have pressure on the bottom
    portion of the movement. You don’t need any slack within the
    band as you lower down along with your front foot. The sumo squat just isn’t as
    well-liked because the sumo deadlift (which is further below).
    Doubtless, it’s because squatting with a wide stance may be
    onerous in your hips. However, with resistance bands, you eliminate most
    of this concern. There won’t be a heavy load in your spine and hips, and the motion feels very good with
    bands.
    Now that we’ve coated all 7 of the killer leg day exercises for energy athletes, let’s take a fast take
    a glance at some applications that incorporate the actions from this listing of
    leg day exercises. Research confirms that to achieve the best energy outputs you want to vary the load
    between 30-70% of your 1RM again squat. The highest total energy is definitely achieved with lower hundreds, the place as the very best power on your quads is achieved with the higher loads.

    One further benefit, the spit squat requires balance and core
    activation to do well. Not solely are you working your legs, but also your abs and the
    stabilizers of the pelvis and spine. These are
    a few of the greatest exercises for constructing athletic legs that look good and
    aren’t just good for trying but actually
    work. Lunge forward with one leg and squat down simultaneously until your back knee
    is a few inches away from the ground.
    Sam Shaw is a certified private trainer and the
    founder of Lemon Studios. He has educated several actors and tv presenters and believes in a holistic method to psychological and physical well being,
    incorporating exercise and vitamin. To assist individuals
    get stronger, healthier, and more assured – all with a
    enjoyable, no-nonsense method.
    Single-joint workouts are necessary, too—these allow you to residence in on a single muscle for more focused training.

    We did this so you’ll be able to escape of your norm and choose
    leg workouts that align together with your coaching targets.

    For those seeking to take their leg workouts to the following
    degree, advanced bodyweight workout routines
    can present the problem needed to continue progressing. One such train is the pistol squat, which demands vital strength, stability, and flexibility.
    To carry out a pistol squat, stand on one leg whereas extending the other leg in front of you.
    Lower your physique down right into a squat while maintaining the extended leg off the bottom.

    References:

    steroids Dbol pills (git.aionnect.com)

    Reply

  159. Regular deadlifts, sometimes called typical deadlifts,
    involve a bent-knee and rounded-back movement. They engage a extensive range of muscle teams, together with the hamstrings, glutes, quadriceps, and decrease
    back. Common deadlifts emphasize concentric loading, the place the muscles
    shorten while lifting the weight. There are two primary explanation why
    you need to do Romanian deadlifts over conventional deadlifts.
    First, RDLs target your posterior muscles (the glutes and hamstrings) more
    intensely than the standard deadlift.
    A place to discuss all 5/3/1 variants, related coaching, techniques, and health considerations.
    One noticeable difference between the Romanian Deadlift
    and the Deadlift lies of their approach and execution. The Romanian deadlift and the
    conventional deadlift are extra comparable than they’re completely different,
    they usually can usually serve as good alternatives
    for each other, relying on the situation. Priorities change,
    and as you transition from being a newbie to a extra skilled lifter, you could wish to try
    different workout routines.
    Think About a scenario the place you want to choose trash up off
    of the bottom. This real-life situation underscores the parallel
    between the traditional deadlift and the demands of precise strength-related challenges.
    The fact is, most of us go to the gym to build
    greater muscular tissues, and that is often accomplished by focusing on main muscles just like the arms, chest, and shoulders–all of
    which may be focused by the deadlift. Each the Romanian Deadlift and the Deadlift are compound
    exercises that interact multiple muscle groups.

    If you do, have the barbell at a height barely above your knees.
    The Romanian deadlift is a very fashionable deadlift version that isolates the posterior chain muscles.
    While it’s known as a “deadlift,” it is significantly completely different from the standard deadlift or some other deadlift variation you might do.
    While the traditional barbell deadlift is the most common deadlift variation, there are countless versions,
    like the sumo deadlift vs. typical, for instance. Interestingly, even events like Atlas Stones seen within the sport of Strongman are a type
    of deadlift.
    Bend over and seize a barbell or dumbbells with an overhand grip.3.
    Squeeze your glutes to lift your torso and return to
    the upright starting place. However there are causes weightlifters have been performing this compound exercise, and its
    variations, for hundreds of years.
    There’s the standard form of the barbell deadlift—but additionally consider
    another variation that makes use of a barbell, the Romanian deadlift (otherwise often identified as the RDL).
    The train is a posterior chain powerhouse, which permits you to target muscle tissue in ways its classic counterpart can’t match.
    The Romanian deadlift just isn’t the identical as the stiff leg deadlift.

    The stiff leg deadlift uses a longer vary of
    movement, the place you deliver the barbell to the bottom vs.
    the Romanian deadlift where you stop just below the knee.
    Moreover, the barbell within the stiff leg deadlift is allowed to come
    off the body, whereas the barbell is instructed to stay on the
    body for the Romanian deadlift. The stiff leg deadlift activates the hamstrings extra
    in contrast with the Romanian deadlift.
    At the lockout place, the right place is standing tall, preserving a neutral
    backbone position. Do not lean your shoulder back putting your self in hyperextension. The work is
    being carried out by your back muscular tissues and legs, not
    your biceps. To transfer the most weight, you need
    to hold your shins perpendicular to the floor and your hips high.

    References:

    Bodybuilding Steroids For Sale

    Reply

Leave a Reply to diseño web donostia Cancel reply

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