8 Replies Latest reply: Apr 30, 2014 5:12 AM by sql_coder RSS

    Turn Non Unique Index into a Unique Index

    sql_coder

      Hello,

       

      Version: 11.2.0.4.0 Enterprise Edition

       

      We got a non unique Index for a primary key constraint. I would like to change that into a unique index. I know you cannot alter the index to change it. Is the only way to disable the primary key, drop the index and enable the constraint again or is there a better solution? It is a 24/7 platform.

       

      sql_colder

        • 1. Re: Turn Non Unique Index into a Unique Index
          sybrand_b

          Service windows exist for a reason.

          Also you can create indices online in an operational database.

          Apart from using DBMS_REDEFINITION there is no other method.

          Thinking of it: what is the business case to change the index?

           

          -----------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: Turn Non Unique Index into a Unique Index
            Dom Brooks

            Obviously there are benefits to having a unique index rather than a non-unique index but what is the driver for the change?

             

            If it were 12c you could do this easily:

            Modify Primary Key Using Index | Richard Foote's Oracle Blog

            • 3. Re: Turn Non Unique Index into a Unique Index
              Top.Gun

              What's wrong with keeping the non-unique index for the primary key constraint?

              • 4. Re: Turn Non Unique Index into a Unique Index
                sql_coder

                there is no buisiness case yet, just wanted to know if there is a good way to change it on the fly. So we will wait till the app servers are down in a service window.

                 

                sql_coder

                • 5. Re: Turn Non Unique Index into a Unique Index
                  Top.Gun

                  There is a business case against..... drop the primary key and the non-unique index remains so you don't need to build it again.

                  • 6. Re: Turn Non Unique Index into a Unique Index
                    Jonathan Lewis

                    sybrand_b wrote:

                     

                    Service windows exist for a reason.

                    Also you can create indices online in an operational database.

                    Apart from using DBMS_REDEFINITION there is no other method.

                    Thinking of it: what is the business case to change the index?

                     

                    -----------

                    Sybrand Bakker

                    Senior Oracle DBA

                     

                    Service windows may not be available very often, and you can't create an index with the same column list as an existing index until 12c.

                    Using non-unique indexes to support unique constraints changes the cost of queries and the volume of redo generated, so it's possible that the business case is that an earlier decision was wrong and needs to be corrected.

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: Turn Non Unique Index into a Unique Index
                      Jonathan Lewis

                      sql_coder wrote:

                       

                      Version: 11.2.0.4.0 Enterprise Edition

                       

                      We got a non unique Index for a primary key constraint. I would like to change that into a unique index. I know you cannot alter the index to change it. Is the only way to disable the primary key, drop the index and enable the constraint again or is there a better solution? It is a 24/7 platform.

                       

                       

                      If you want the index to have exactly the same column definition then you have to fiddle around a little bit (and use extra resources) if you want to avoid any down-time. The trick is to create an intermediate index to support the constraint, drop the old PK index, create a new PK index, then drop the intermediate one; e.g.

                       

                      create table t1 as select * from all_objects where rownum <= 10000;

                      create index t1_pk on t1(object_id);

                       

                      alter table t1 add constraint t1_pk primary key(object_id);

                       

                      create index t1_i1 on t1(object_id, 0) online;

                      alter table t1 modify primary key using index t1_i1;

                       

                      drop index t1_pk;

                       

                      create unique index t1_pk on t1(object_id) online;

                      alter table t1 modify primary key using index t1_pk;

                       

                      drop index t1_i1;

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Turn Non Unique Index into a Unique Index
                        sql_coder

                        hello Jonathan,

                         

                        that is a very nice solution, thanks for it. One more suggestion, the index t1_i1 could be Unique already. Another nice way (if you have a service window), diable the primary key, drop the index, enable the primary key again.

                         

                        sql_coder