11 Replies Latest reply: Jul 24, 2013 5:08 PM by rp0428 RSS

    Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates

    nikos

      I am trying the new Oracle 12c and its feature to create columns with the keyword IDENTITY.

       

      I create a table

       

      CREATE TABLE xt (a NUMBER GENERATED by default AS IDENTITY PRIMARY KEY, b VARCHAR2(10));

       

      And populate it, sometimes specifying the value for a and sometimes relying on the system to generate the value:

       

      INSERT INTO xt (b) values ('a');

      INSERT INTO xt (b, a) values ('b', default);

      INSERT INTO xt (b, a) values ('c', 3);

      INSERT INTO xt (b) values ('d');

      INSERT INTO xt (b) values ('e');

       

      The problem is that the fourth INSERT fails because the system tries to use the value 3 which is already taken.  The fifth statement gets the value 4. And the table now contains:

       

      A B

      - -

      1 a

      2 b

      3 c

      4 e

       

      Is there something I am missing? I understood that by specifying BY DEFAULT, I would be allowed to sometimes specify values on my own without them interfering with the generated values. I now that the same thing works correctly in MySQL (where I would get five rows from 1 to 5 with the same INSERT statements)

       

      /nikos

        • 1. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
          Sven W.

          First: You never should feel the need to create your own identity values. This is especially dangerous when thinking about a multi user environment. Lets say you have a process that inserts ID 17 and another session (different user) tries to do the same. Then the numbers will clash, beacuse of the poor programming logic.

           

          What happens in your case is that oracle creates a sequence for this identity column and uses this sequence to populate the values. This automatically created sequence has a name like ISEQ$$_12345

          What you can do is fetching a new ID value from this sequence generator and use this number (ISEQ$$_12345.nextval). Oracle will not try to reuse the same number if it was fetched from the same sequence.

           

          Remember: If you want to know which id was used, e.g. your process first adds some parent table data, then some detail table data. And you need the ID value for the detail table. That is what the returning clause is for.

           

          pl/sql example

          declare
            v_name myTable.name%type;
            v_id      myTable.id%type;
          begin 
            v_name := 'Paul Allan';
            insert into myTable (full_name) 
            values (v_name) 
            returning id into v_id;
          
           -- Now use v_id to insert data into the details tab. 
          end;
          
          • 2. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
            nikos

            That is not the point. I know how to work around this and I would in practice never design a situation where I use both generated and non-generated values. The issue is with the behavior as described above. The documentation implies that a column created in this way will get unique values, but it doesn't. If the BY DEFAULT is not working correctly, it would be better to remove it and have IDENTITY behave as ALWAYS. Even in the examples provided by oracle experts and in documents describing "What's new in oracle 12c" this mixing of generated and non-generated values is present. And it is also mentioned that this functionality is "as in MySQL", but obviously it is not. I don't see in the documentation any keyword that can be added to configure the generated values to skip inserted values.

            • 3. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
              Sven W.

              I understand that if you come from another database then you wish to have each feature implemented identically, even if it was a bad design (meaning badly scalable for massive multiuser environments or for larger tables) in the first place.

               

               

              I can't find an example in the oracle docs where a value is inserted. The only example I see, is where a NULL value is inserted and then the DEFAULT ON NULL AS IDENTITY kicks in. See here:http://docs.oracle.com/cd/E16655_01/gateways.121/e22508/migr_tools_feat.htm#DRDAA109

               

               

              Oracle states that the identity column now is ANSI SQL conform. The standard implies that there are two ways

              a) ALWAYS AS IDENTITY

              b) BY DEFAULT AS IDENTITY

               

               

              Oracle incudes both options: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#CJAHJHJC

               

               

              identity_clause

              Use this clause to specify an identity column. The identity column will be assigned an increasing or decreasing integer value from a sequence generator for each subsequent INSERT statement. You can use the identity_options clause to configure the sequence generator.

              ALWAYS If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned. This is the default.

              BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column. If you specify ON NULL, then Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

               

              You tested the second behaviour. In this case the database can not be made responsible for the values that a user enters.

               

              The documentation never states that it will behave like the MySql AUTO_INCREMENT column, which is not standard conform.

               

              A good overview about some databases and the implementation of autoincrement: Comparison of different SQL implementations

              The 12c Oracle version is missing, but the overview is nevertheless very nice.

              • 4. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                SomeoneElse

                > to configure the generated values to skip inserted values.

                 

                Wouldn't that possibly incur enormous overhead?

                • 5. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                  nikos

                  I understand everything you say and I could even accept that the behavior in Oracle 12 is both correct and desired. But it is still misleading when the user may believe that manually inserted values will be skipped when the system generates values. Especially since this is the case in products like MySQL and SQL Server.

                   

                  Here is the article that mixes generated and fixed values:

                  http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php

                   

                  In the first paragraph there is also a mention that this would be the equivalent of Autonumber of identity in other products (probably referring to SQL Server and Access)

                   

                  And here is a page that explicitly mentions SQL Server and Access:

                  http://www.oracle-base.com/articles/misc/autonumber-and-identity.php

                   

                  The mention to auto_increment of MySQL was on this page and I have no idea what the blogger's relationship is to Oracle:

                  http://satya-dba.blogspot.se/2012/10/new-features-in-oracle-database-12c.html

                   

                  So the conclusion should be at least that this detail should be clearly mentioned in the documentation in order to avoid confusion, and at most that the implementation should be changed.

                  • 6. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                    Sven W.

                    All the articles that you linked are not official oracle documenations. Nevertheless they are all good articles. However you should read them as recommendations and comparisons. Phrases like "equivalent of" or "like in MySql" should be interpreted as "similiar to" not as "identical to".

                     

                    In my opinion the oracle documentation is clear. But I work with oracle since many years and know how seqeunces work there. Therefore I'm not surprised at all, that if I enter values into my ID column, that the sequence generated IDs eventually will trip over those values.

                     

                    I guess your confusion comes from the fact that you are used to other databases that handle such autoincrement columns differently. For example MS ACCESS (which I do not consider a proper database) will check the maximum value add +1 and puts that value into the autoincrement column. Drawback: MS ACCESS is single user only (even in "multi user mode" only one user at a time can write). And it is terribly slow. When you move from a single user system to a multi user system you have to adapt. Some rules can be reused, some others not.

                    • 7. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                      rp0428

                      I agree with the other responders and, IMHO, the IDENTITY functionality is working as Oracle designed and intended it to.

                       

                      But as with any NEW functionality that Oracle introduces all we can comment on is how a given functionality works (or appears to work).

                       

                      Only Oracle knows for sure how they the functionality is SUPPOSED to work.

                       

                      If you still believe there is a 'bug' in the IDENTITY functionality or even a problem with the documentation you should open an SOR with Oracle.

                      • 8. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                        Pacmann

                        I agree with Nikos.

                         

                        but you can also explicitly assign a specified value to the column


                        At least, the documentation could have been clearer about this by telling that it is not a good idea, like in the limitations paragraph where it describes cases which might be troublesome...

                        • 9. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                          rp0428

                          Pacmann wrote:

                           

                           

                          At least, the documentation could have been clearer about this by telling that it is not a good idea, like in the limitations paragraph where it describes cases which might be troublesome...

                          I don't see any such 'limitations' paragraph at that link for any section or anything about 'troublesome' cases.

                           

                          There is a 'Restrictions on Identity Columns' sections that specifies what can NOT be done

                           

                          It would be a real 'can of worms' for Oracle to try to include warnings about all of the things that people might do to 'misuse' their functionality.

                           

                          This functionality really has no issues that any existing functionality that uses a sequence doesn't already have. Users already have tables with triggers that use sequences to generate 'id' values and in some cases those triggers only generate them if the column is already null.

                           

                          So for me this falls into the category of things you should not do if you don't know what you are doing. The developers need to be trained not to provide either the column or a value for it and let Oracle do the work. On those rare occasions that a value needs to be manually provided the developer needs to make sure there won't be a conflict with an existing or future value.

                          • 10. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                            Pacmann

                            Yeah, no limitations, but restrictions : sorry about my poor english.

                            Now, in the restrictions, i can read :

                            CREATE TABLE AS SELECT will not inherit the identity property on a column.

                             

                            Why not an additional restriction :

                            " When you explicitly assign a specified value to the column, next default insertion will crash"

                             

                            So for me this falls into the category of things you should not do if you don't know what you are doing. The developers need to be trained not to provide either the column or a value for it and let Oracle do the work.

                             

                            Of course, we don't need any law, people should know what they do. Of course, i guess you never saw any warning about something that seems obvious for developer knowing their job ?

                            • 11. Re: Oracle 12c - GENERATED BY DEFAULT AS IDENTITY generates duplicates
                              rp0428

                              Why not an additional restriction :

                              " When you explicitly assign a specified value to the column, next default insertion will crash"

                               

                              Because that statement is NOT true!

                               

                              You can test that using a similar example to what nikos posted.

                               

                              Create the table like OP did but specify a START WITH value of one million

                              >

                              CREATE TABLE xt (a NUMBER GENERATED by default AS IDENTITY

                              (START WITH 1000000)

                              PRIMARY KEY, b VARCHAR2(10))

                              >

                              Now the sequence will use values from one million and higher. Now you can insert values less than one million and there will be no 'crash' unless you insert a value that another developer inserted before.

                               

                              Using a specifc START WITH value is an easy way to avoid conflicts should the need arise to manually insert a row that uses a provided value.