Auto Increment Column Performance Enhancement with each Oracle Version

With Oracle 10g to Oracle 11g and now in Oracle 12c, there were enhancements made on how to maintain an Auto Increment Column (mostly primary key) with sequence, trigger and identity column. With this post I am trying to compare the performance enhancement with every Oracle Version for maintaining a Auto Increment Primary Key Column. I will be focusing on following methods in this post
1) Sequence value assignment using SQL (Oracle 10g or earlier)
2) Direct Sequence Assignment in PL/SQL (Oracle 11g)
3) Sequence as default value (Oracle 12c)
4) Generated always as identity (Oracle 12c)

1) Oracle 10g or earlier versions, Sequence value assignment using SQL.
SQL> create table sequence_test
  2  (
  3     id number,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> create sequence seq_sequence_test start with 1 increment by 1 nocache nocycle;
Sequence created.

SQL> create or replace trigger trg_sequence_test
  2  before insert on sequence_test
  3  for each row
  4  begin
  5      select seq_sequence_test.nextval into :new.id
  6      from dual;
  7  end;
  8  /
Trigger created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050168 rows created.

Elapsed: 00:02:48.30
Took 2 minutes and 48 seconds to insert little over 1 Million records. Lets check the performance for the enhancement done for Oracle 11g.


2) Oracle 11g, Direct Sequence Assignment
SQL> drop table sequence_test purge;
Table dropped.

SQL> drop sequence seq_sequence_test;
Sequence dropped.

SQL> create table sequence_test
  2  (
  3     id number,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> create sequence seq_sequence_test start with 1 increment by 1 nocache nocycle;
Sequence created.

SQL> create or replace trigger trg_sequence_test
  2  before insert on sequence_test
  3  for each row
  4  begin
  5     :new.id := seq_sequence_test.nextval;
  6  end;
  7  /
Trigger created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050168 rows created.

Elapsed: 00:02:44.60
As we can notice, the executing time is almost same as of Oracle 10g method. Oracle 11g direct sequence assignment provides simplicity but not the performance as Oracle has not changed the implementation of fetching value from sequence.


3) Oracle 12c, Sequence as default value
SQL> drop table sequence_test purge;
Table dropped.

SQL> drop sequence seq_sequence_test;
Sequence dropped.

SQL> create sequence seq_sequence_test start with 1 increment by 1 nocache nocycle;
Sequence created.

SQL> create table sequence_test
  2  (
  3     id number default seq_sequence_test.nextval,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050112 rows created.

Elapsed: 00:01:53.47
With Oracle 12c enhancements Sequence as Default Value, there is a lot of improvement with sequence performance and we no longer need to use triggers for surrogate primary key and by this we can save the expensive context switch. But it is little risky as someone can overwrite the sequence value if pass any value in primary key column.


4) Oracle 12c, GENERATED ALWAYS AS IDENTITY (My Personal Favorite)
SQL> drop table sequence_test purge;
Table dropped.

SQL> drop sequence seq_sequence_test;
Sequence dropped.

SQL> create table sequence_test
  2  (
  3     id number GENERATED ALWAYS AS IDENTITY,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050112 rows created.

Elapsed: 00:01:52.69
It is almost same as Sequence as default value of Oracle 12c but if we compare it with Oracle 11g or earlier, we can see alot of performance gain. Benefit of GENERATED ALWAYS AS IDENTITY are
a) we do not create sequence (oracle will do it for us)
b) Oracle database will always use sequence value and will throw an error if you attempt to explicitly assign a value to the column using INSERT or UPDATE.
c) performance is highly improved over Oracle 11g or prior methods


I hope that you have enjoyed reading this post. Please comment your feedback and suggestions.

Related Posts:
- Sequence Behavior with Multitable Insert All
- Oracle Database 12c New Features for Developers
- Top 15 new features of Oracle Database 12.2 for developers
- Oracle Auto Increment Column - Sequence as Default Value
- Setting Sequence Value to a Specific Number
- Sequence: NEXTVAL, CURRVAL and SESSION
- USER_SEQUENCES.LAST_NUMBER AND SEQUENCE CACHE
- Complex View Merging Transformation and ORA-00979 in Oracle 12c

85 comments:

  1. equence as default to column is a good feature. If you cache sequence in 1000 then you may get even improved performance.
    Identity is available in other databases for many versions and it was always in my wish list for Oracle to implement.
    Thanks for sharing this.

    Devang Shah
    Database Architect

    ReplyDelete
  2. Personally, I like sequences better the auto increment columns. I like being able to see where the sequence numbers are in the database especially if you have do a natural key. Personally, I like artificial keys, enforces uniqueness through a index.

    ReplyDelete
  3. SQL> create or replace trigger trg_sequence_test
    before insert on sequence_test
    for each row
    begin
    If :new.id is null then
    :new.id := seq_sequence_test.nextval;
    end if;
    end;
    /

    This soluction allows inform or not the key in the INSERT commands.

    ReplyDelete
  4. so Oracle discovered in 12c the identity with auto-increment that MS SQL has at least SQL 2000 :) it was obvious that 1 mil.+ calls of trigger and sequence would take quite some time


    George Novac
    Head of BI Office @ Cosmote RMT SA

    ReplyDelete
  5. Sequence is an old and reliable approach, but in multiuser apps due to caching there maybe spans between sequential values. For example, when application fails and must be reconnected to database then all cached values are missed. It's interesting for me what behaivour of Oracle 12c identity column in such situations? The performance about the same as for sequence, what is a benefit?

    ReplyDelete
    Replies
    1. oh, there is a performance gain. sequence+trigger based implementation took 02 minutes 44 seconds and IDENTITY took only 01 minute 52 seconds in my demo.

      Delete
  6. I have used both Ss and oracle over the years, and this was one of only a handful of things oracle lacked, so it's great it now has it. However I don't follow why a sequence would have ever been called in a trigger, for a million rows, from what I gather is a loop with an after insert tigger. if doing bulk inserts just insert it as a value, all at once. I have more than likely not understood the context.

    ~Andrew Bennett

    ReplyDelete
  7. after over 10 years of Oracle experience, a year before, I had been working on Sql Server and found many cute stuffs... ease Temporary table, Auto increment. However still hard to rely on @@identity for batch processing while numbers of concurrent session using the same identity... SS mess it up

    ReplyDelete
  8. All you SQL Server lovers can only speak about this, which is bottom line not a problem if you are not lazy enough to write 2-3 lines of code. For the rest, SQL server eats the dust from Oracle....and worst, you know it!!!!!! :)

    ReplyDelete
  9. Would like to share A Great Advice by Steven Feuerstein on using sequences with triggers
    Me: direct sequence assignment does the same behind the scene, so I don't mind selecting nextval from dual.
    SF: U might not mind, but developers in future could, when/if @OracleDatabase changes implementation and avoids select from dual.
    SF: Always choose the highest level most declarative implementation possible!

    ReplyDelete
  10. Spot on with this write-up, I honestly feel this amazing site needs a
    great deal more attention. I'll probably be returning to see more, thanks
    for the info!

    ReplyDelete
  11. Nice blog right here! Additionally your website rather
    a lot up fast! What web host are you using? Can I get your affiliate hyperlink for your host?
    I desire my site loaded up as quickly as yours lol

    ReplyDelete
  12. We're a gaggle of volunteers and starting
    a brand new scheme in our community. Your web site provided us with helpful info to work on. You have performed a formidable job and our entire group might
    be thankful to you.

    ReplyDelete
  13. Wow! Thank you! I constantly needed to write on my site something like that.
    Can I implement a fragment of your post to my website?

    ReplyDelete
  14. I like this post, enjoyed this one regards for posting.

    ReplyDelete
  15. I got what you mean,saved to fav, very nice
    website.

    ReplyDelete
  16. I like this site very much so much wonderful info.

    ReplyDelete
  17. Perfectly indited subject matter, Really enjoyed studying.

    ReplyDelete
  18. Thank you for sharing with us, I conceive this website truly stands out :D.

    ReplyDelete
  19. Great information once again! I am looking forward for more updates;)

    ReplyDelete
  20. Interessante este artigo que acabei de ler neste blog, gostei salvei até em meus favoritos no navegador.
    Número do Balanço Geral

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Excellent post, its amazing that its necessary really but it is. I think Iarfhlaith's point about bigger buttons attracting more comments is very true. I'm subtle that way.
    cash for cars ipswich
    sell your car Ipswich

    ReplyDelete
  23. Cash for cars Adelaide is a convenient and rewarding option for you to sell your car. You can sell your used, scrap, unwanted or broken car almost immediately. There are many ways you can use this cash for cars Adelaide' options such as selling your car towing services, private traders, car auctions, online dealerships etc.
    Regard: cars for cash adelaide

    ReplyDelete
  24. Speedy Cash for Cars is an attractive new option for car owners in Brisbane who want to sell salvaged vehicles or want to get rid of old scrap. We provide a one-stop solution to all your car removal troubles.

    bus wreckers brisbane

    ReplyDelete
  25. Cash for Cars can be regarded as the most convenient and fast way to dispose off your old free car removal Brisbane today. They are experts in accepting all kinds of vehicles, be it cars, bikes or even farm vehicles.
    Regard: cash for scrap cars

    ReplyDelete
  26. Hello there! I just want to offer you a big thumbs up for your great info you have right here on this post. I'll be coming back to your web site for more soon.
    cash for car removal gold coast
    car for cash gold coast

    ReplyDelete
  27. The next time I read a blog, I hope that it does not fail me just as much as this particular one. I mean, I know it was my choice to read, nonetheless I actually believed you would probably have something useful to talk about. All I hear is a bunch of whining about something you could fix if you were not too busy looking for attention.
    cash for cars ipswich
    cash for car ipswich

    ReplyDelete
  28. I truly love your blog.. Excellent colors & theme. Did you create this website yourself? Please reply back as I’m planning to create my own personal website and would love to learn where you got this from or what the theme is called. Kudos!
    sell cars for cash
    cash for cars brisbane

    ReplyDelete
  29. Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. 増田裕介

    ReplyDelete
  30. Thanks for sharing your knowledge. I’m sure it will help me for cash for cash for cars Bisbane service. Your blog is very great and helpful for unwanted car services.
    cash for cars brisbane

    ReplyDelete
  31. Great post, you have pointed out some fantastic points , I likewise think this s a very wonderful website. csgo high tier accounts

    ReplyDelete
  32. OwnASmurf offers you the CSGO Smurf Accounts at the best & cheap price. Every customer can afford the rate of CSGO Accounts.

    For detail visit: https://ownasmurf.com/product-category/csgo-prime-accounts/

    ReplyDelete
  33. Great information. Thanks for sharing this article. This blog is very informative. It really helps those who are struggling to provide services in cash for cars caboolture. I have already followed your blog.
    cash for cars caboolture

    ReplyDelete
  34. This is such a nice information which I have found in google search thank you. Get rid of used scrap cars for cash anywhere in Sydney with Cash For Cars Sydney. We also provide a free old car removal service for our clients.

    ReplyDelete
  35. Cash For Car Sydney are the top buyers of all types of old vehicles and can come to your place to tow it away for free. You can trust Sydney Cash For Cars for its citywide services. None can beat us at prices for unwanted cars.

    ReplyDelete
  36. Great post shared I really need this post your blog is very useful to me. High-class Gaming Marketplace for Buy and Sell Games, Online games Account.
    High-class Gaming Marketplace
    Best Games Trading Platform
    Customer Reviews of Esports4g

    ReplyDelete
  37. Great and informative. Well explained with proper example. Kudos!!
    Brisbane cash for car offers several options to car owners so get
    Free car removal Ipswich
    Free car removal Gold coast
    cash for caravan and machinery
    cash for cars
    cash for cars Brisbane
    cash for cars Gold coast

    ReplyDelete
  38. If you do not know how to take care of your car and when to take it to the mechanic, We have some valuable Car Safety Tips To Avoid Road Accidents. Read on to know how you can ensure the safety and long life of your car.

    ReplyDelete
  39. Master Cash For Cars offers free car collections Sydney wide and even pay you on the spot for your vehicle regardless of what shape it’s in. Our expert tow drivers can help you in this regard with their quick service of removing cars from your property. We require information about the vehicle such as Make, Model, Year Made and condition. Our Sydney scrap car buyer ready to buy your old, scrap, damaged and unwanted car for cash. Just contact us with your vehicle information, and we will handle the rest. Book Free Scrap Car Collection Services Today on 0289741445! Contact us through call, email, or our online quote form.

    ReplyDelete
  40. Looking to get a good option for your car removal in Sydney? There are few suggestions that may helpful for the car owners to sell their unwanted vehicles for good price. They are helping peoples to tow their cars, vans trucks and 4wd in anywhere in the Sydney and best thing they don't charge for any quotation.

    ReplyDelete
  41. I read this Blog. This is a nice and very useful blog. Are you facing the problem with Car, don't worry, need more info about this issue please click here for know more
    House Renovations in Richmond

    ReplyDelete
  42. Thanks for your post, it has inspired me greatly. I have enjoyed reading your blog, love your work! Cash For Cars Brisbane, Cash For Cars Caboolture, Scrap Car Removals.

    ReplyDelete
  43. Once you've imported your Contact Intelligence - Lix, you'll need a tool to export them to your computer. Once you've downloaded the tool, you'll need to import the data into your spreadsheet or excel.

    ReplyDelete
  44. Excellent Blog! I would like to thank you for the efforts you have made in writing this post. Reported calls

    ReplyDelete
  45. Your information is very helpful for us thanks for sharing the valuable information.Reported calls

    ReplyDelete
  46. You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this... used auto parts

    ReplyDelete
  47. A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. concrete bricks waste removal

    ReplyDelete
  48. Thanks for sharing this useful content. Own your dream car on sale from the Alba Cars. UAE #1 Pre-Owned Car Showroom for buying and selling used luxury cars.

    used cars in dubai
    used cars dubai

    ReplyDelete
  49. I recently found many useful information in your website especially this blog page. Among the lots of comments on your articles. Thanks for sharing.
    Mining

    ReplyDelete
  50. This post is Observable as you bring updates from time to time.

    Tractor Price

    ReplyDelete
  51. This comment has been removed by the author.

    ReplyDelete
  52. This comment has been removed by the author.

    ReplyDelete
  53. You provided excellent solution to mine problem thanks buddy.
    Mahindra Supro T2 Mileage

    ReplyDelete
  54. Thanks for sharing this unique and valuable blog with us.

    popular tractor price

    ReplyDelete
  55. I'm glad I came into this article because it provides a lot of important information.

    tractor price in india

    ReplyDelete
  56. I value all your blog posts, and this is genuinely a fantastic article. Your knowledge is highly insightful.

    CEAT tractor tyre

    ReplyDelete
  57. I appreciate several of the facts composed by yours.

    Rear Tractor Tyre

    ReplyDelete
  58. I want to learn programming. My interest is in C, C++ and Java.
    Tata Intra V30

    ReplyDelete
  59. Nice information, I am heartily very thankful to you for providing this kind of special knowledge. This information will always help everyone for gaining knowledge. Always keep sharing. Thanks. used tractor

    ReplyDelete
  60. Thanks, foг ones marvelous posting! I genuinely enjoyed reading it, you miggһt Ƅe a great author. No.1 Scrap Metal Buyer In Queensland Australia, BNE Copper Recyclers buy all kinds of cash for lead from your residential and commercial areas in QLD and its regions.! Our prices are best in Brisbane, Gold Coast, Toowoomba, Ipswich, Sunshine Coast, Caboolture and we won’t charge you any hidden fees.

    ReplyDelete
  61. Thanks for sharing this useful content. Now buying used cars in Dubai, UAE is very easy. Alba Cars gives you online benefits such as certified second hand cars in dubai, EMI along with inspection of your old car. Find great deals on good condition used cars in UAE . Visit for Used Cars For Sale & sell used car

    ReplyDelete
  62. Cash for cars Newcastle is a car buying service that buys your car and pays you cash in Newcastle Australia. The process is quick and easy, just visit the website and answer a few questions about your car. From there you will be given a unique code which can be used to take your car to the nearest Cash for cars Newcastle location. Once there, you will be given a quote and then the process begins. It's that easy!

    ReplyDelete
  63. Connect Resources aspires to provide an incredible opportunity for the UAE Nationals by offering specialized Training program across various spheres, Such as International Sales, Local Sales, Marketing, IT, Recruitment, Admin, HR, Legal, PRO, Customer Success, Finance & Accounting and Business Setup. emirati graduate program uae

    ReplyDelete
  64. Great, I thought your article was original, keep up the excellent work, we love reading your blog, and please keep sharing content like this. - Sonalika 750

    ReplyDelete
  65. Thanks for sharing such a great Information
    used cars in dubai

    ReplyDelete
  66. Appreciating the time and effort you put into your website. Searching to remove your Car for Cash? Metal Biz Recyclers warm-heartedly greets every customer with any car they have. With our 100% customer satisfaction, you can easily get Cash for cars Brisbane up to $9,999.

    ReplyDelete
  67. I also wanted to get Top Cash for Cars Brisbane. Thanks To God I came across this content. It helped me better understand about such service provider. Do read it out!

    ReplyDelete
  68. We appreciate you sharing this informative article with us. Please keep up the great work. If you have any old or used cars that need to be disposed of urgently, then check out our Immediate Steps to Follow After Car Accident. Read here full article carefully.

    ReplyDelete
  69. We offer a simple and easy solution to get it off your hands. At Old Car Removal Sydney, we offer to buy scrap cars for instant cash up to $9,999.

    ReplyDelete
  70. Au Cash For Cars is the highest rated cash for cars company in the QLD area. We cover Gold Coast, Brisbane, Logan, Ipswich, Toowoomba, and the Redland Bay area.

    ReplyDelete
  71. With Cash for Cars Brisbane , You Are In Good Hands. Perks We Offer Includes No-Obligation Quote, Hassle-free Pick-Up, Free Car Removal in Brisbane, 24/7 Online Support

    ReplyDelete
  72. Thanks for sharing such wonderful piece of content! cars in dubai

    ReplyDelete
  73. You have provided important data for us. It is valuable and informative for everyone. Thanks
    New and Pre-Owned luxury cars

    ReplyDelete
  74. where challenges are seen as opportunities to learn and improve, can significantly impact one's ability to excel. Cultivating resilience and maintaining a positive attitude in the face of setbacks are essential components of a high-performance mindset. slot gacor hair ini

    ReplyDelete
  75. pg slot thailand official เว็บแท้ นับเป็นแหล่งรวมเกมสล็อตที่ให้ประสบการณ์การเล่นที่น่าตื่นเต้นและสนุกสนานมากที่สุดในออนไลน์ด้วยการนำเสนอหลากหลายเกม PG ที่มีกราฟิกที่สวยงาม

    ReplyDelete
  76. Globalscraptrading.org truly stands out as the go-to destination for impeccable scrap trading services in the UAE.
    All the services provided by Globalscraptrading.org Are given Below
    Copper scrap for buying in UAE

    Aluminum scrap for buying in UAE
    Plastic Scrap for Buy in UAE

    Iron scrap for buy in UAE

    Chemical Scrap For Buy in UAE

    ReplyDelete