11 Replies Latest reply: Feb 6, 2014 1:11 PM by JohnWatson RSS

    When do we use unique index for PK?

    user8875620

      I was reading this part of the oracle manual:

      http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11727

       

      I got confused since I was trying to research why sometimes we want to create a primary key using an unique index.

       

      CREATE UNIQUE INDEX MYTABLE_PK ON MYTABLE

      (START_DT, DOC_ID)

      LOGGING

      TABLESPACE TBS_INDEX01

      NOPARALLEL;

       

      ALTER TABLE MYTABLE ADD (

        CONSTRAINT MYTABLE_PK

        PRIMARY KEY

        (START_DT, DOC_ID)

        USING INDEX MYTABLE_PK);

       

       

      Versus sometimes we just simply create the primary key without using an unique index.

       

      ALTER TABLE MYTABLE ADD (

            CONSTRAINT PK_MYTABLE

           PRIMARY KEY

           (START_DT, DOC_ID)

           USING INDEX);

       

       

      When do we use unique index for PK?

        • 1. Re: When do we use unique index for PK?
          Martin Preiss

          if my memory serves me well in earlier versions it was not possible to keep the index when you dropped the constraint - these days there is the KEEP INDEXES clause of DROP CONSTRAINT and I don't see many reasons to create the index separately.

          • 2. Re: When do we use unique index for PK?
            Aman....

            Besides the reason Martin gave, another thought that's coming to mind is that if you want to separate the index's tablespace from the table(a PK index is in the same tablespace as of the table's) , you can use the clause that you have mentioned in your post.

             

            HTH

            Aman....

            • 3. Re: When do we use unique index for PK?
              JohnWatson

              You do not need a unique index for a unique or primary key. A non-unique index is better. Why is it better? Because you can disable the constraint without destroying the index.

              So: create non-unique indexes first, then create the constraints. The idea that unique indexes and unique constraints are the same thing dates back to release 6. They are separate objects, and should be managed separately.

              • 4. Re: When do we use unique index for PK?
                Mark D Powell

                John, While the use of a non-unique index to support a PK is required when the use of deferred constraint enforcement is desired there are CBO related reasons to use a unique index to support PK and UK constraints where possible.

                - -

                I know Richard Foote has blogged on some of the differences and maybe Jonathan Lewis has also.  I do not know if this is the best link but it is on the general subject and mentions some of the costs associated with using non-unique indexes

                http://richardfoote.wordpress.com/2007/12/30/differences-between-unique-and-non-unique-indexes-part-iii/

                - -

                HTH -- Mark D Powell --

                • 5. Re: When do we use unique index for PK?
                  JohnWatson

                  Thank you for replying, Mark, but I don't agree. I'm sure Richard Foote's hypothesis was correct when he wrote it, but I've just tested against the current release. This routine gives me exactly equal figures for the redo and undo in both cases:

                   

                  create table tui(c1 varchar2(100));

                  create unique index tui_i on tui(c1);

                  alter table tui add  constraint tui_uk unique (c1);

                   

                  create table tni(c1 varchar2(100));

                  create index tni_i on tui(c1);

                  alter table tni add  constraint tni_uk unique (c1);

                   

                  insert into tui values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

                  insert into tni values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

                  commit;

                   

                  select name,value from v$mystat join v$statname using(statistic#) where name in ('redo size','undo change vector size');

                  insert into tui values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

                  select name,value from v$mystat join v$statname using(statistic#) where name in ('redo size','undo change vector size');

                  insert into tni values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

                  select name,value from v$mystat join v$statname using(statistic#) where name in ('redo size','undo change vector size');

                   

                   

                  I am of course open to correction on this. The above is a very simple test.

                  • 6. Re: When do we use unique index for PK?
                    Mark D Powell

                    John, the undo/redo issue may be fixed however there were also blogs on the effect that the choice has on the CBO path that need to be considered.  I did not have time to hunt up the articles.  As you point out things change with new releases and beliefs need to be re-tested.  I can remember that in the version 8 manuals (probably DBA or Concepts) where the use on non-unique indexes was introduced the recommendation in the manual was to use non-unique indexes for all indexes.  However, there were several bugs related to the feature and this recommendation was not present that I could see in the 8.1 manuals.

                     

                    For now I intend to stick to recommending the use of unique indexes to support unique constraints and avoiding the use of deferred constraints for the most part.

                     

                    IHMO -- Mark D Powell --


                    • 7. Re: When do we use unique index for PK?
                      Martin Preiss

                      John,

                      I think the better performance is mostly related to query access: a unique scan uses 2 consistent gets for a single row access while an index range scan needs 3 LIOs for the same operation:

                      -- 12.1.0.1

                      drop table t42;

                       

                      create table t42

                      as

                      select rownum id

                           , 'Ziggy' col2

                        from dual

                      connect by level <= 42;

                       

                      exec dbms_stats.gather_table_stats(user, 'T42')

                       

                      create index t42_idx on t42(id);

                       

                      select *

                        from t42

                      where id = 1;

                       

                      set autot trace

                       

                      select *

                        from t42

                      where id = 1;

                       

                      set autot off

                       

                      drop index t42_idx;

                       

                      create unique index t42_uidx on t42(id);

                       

                      select *

                        from t42

                      where id = 1;

                       

                      set autot trace

                       

                      select *

                        from t42

                      where id = 1;

                       

                      set autot off

                       

                      -- results:

                      -----------------------------------------------------------------------------------------------

                      | Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

                      -----------------------------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT                    |         |     1 |     9 |     2   (0)| 00:00:01 |

                      |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T42     |     1 |     9 |     2   (0)| 00:00:01 |

                      |*  2 |   INDEX RANGE SCAN                  | T42_IDX |     1 |       |     1   (0)| 00:00:01 |

                      -----------------------------------------------------------------------------------------------

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                         2 - access("ID"=1)

                       

                      Statistiken

                      ----------------------------------------------------------

                                0  recursive calls

                                0  db block gets

                                3  consistent gets

                                0  physical reads

                                0  redo size

                              619  bytes sent via SQL*Net to client

                              544  bytes received via SQL*Net from client

                                2  SQL*Net roundtrips to/from client

                                0  sorts (memory)

                                0  sorts (disk)

                                1  rows processed

                       

                       

                      ----------------------------------------------------------------------------------------

                      | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

                      ----------------------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT            |          |     1 |     9 |     1   (0)| 00:00:01 |

                      |   1 |  TABLE ACCESS BY INDEX ROWID| T42      |     1 |     9 |     1   (0)| 00:00:01 |

                      |*  2 |   INDEX UNIQUE SCAN         | T42_UIDX |     1 |       |     0   (0)| 00:00:01 |

                      ----------------------------------------------------------------------------------------

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                         2 - access("ID"=1)

                       

                      Statistiken

                      ----------------------------------------------------------

                                0  recursive calls

                                0  db block gets

                                2  consistent gets

                                0  physical reads

                                0  redo size

                              475  bytes sent via SQL*Net to client

                              533  bytes received via SQL*Net from client

                                1  SQL*Net roundtrips to/from client

                                0  sorts (memory)

                                0  sorts (disk)

                                1  rows processed

                      (I am quite sure that the test was created using an example in Richard Foote's blog, but I don't find the reference).

                       

                      But I see also relevant administrative advantages of using non-unique indexes - most important the possibility to disable a non-unique index for insert append operations that bring ora-1502 when a unique index is in place:

                      drop table t;

                       

                      create table t (id number);

                       

                      create unique index t_uix1 on t(id);

                      alter index t_uix1 unusable;

                       

                      insert into t

                      select rownum id

                        from dual

                      connect by level < 10000;    

                       

                      insert into t

                      *

                      FEHLER in Zeile 1:

                      ORA-01502: Index 'C##TEST.T_UIX1' oder Partition dieses Index in nicht brauchbarem Zustand

                      • 8. Re: When do we use unique index for PK?
                        Martin Preiss

                        the missing reference was http://richardfoote.wordpress.com/2009/05/13/indexes-and-small-tables-part-v-its-no-game/.

                         

                        Randolf Geist mentioned a more special case of different behaviour concerning NESTED LOOPS joins: http://oracle-randolf.blogspot.de/2011/07/logical-io-evolution-part-1-baseline.html.

                        • 9. Re: When do we use unique index for PK?
                          JamesGordon

                          You are basically doing the same thing either way. One way you are precreating the unique index and then when you create PK it seems the existing inidex and uses it. The other way it sees that a unique index doesn't exist and creates it to support the PK.

                          • 10. Re: When do we use unique index for PK?
                            jgarry

                            As the other replies have shown, this question can run deep.  In addition to the optimizer sometimes using additional information to help (as in the Richard Foote blog link, and I also vaguely recall Jonathan has demos of the mere existence of indices influencing), sometimes it doesn't use it where one might expect: PK Problem | Oracle Scratchpad

                             

                            So it's kind of a toss-up whether to assume Oracle will do the right thing when making assumptions about relations.  I put it that way because John's habit is the opposite of what relational theory would say, that is, properly normalized primary keys must be unique.  John's is not bad advice, unless it is applied everywhere without thought or investigation.  Scaling in volume, repetition or concurrency can have serious effects.

                            • 11. Re: When do we use unique index for PK?
                              JohnWatson

                              James, you have returned the thread to the question.

                              I have to apologize: sorry, user8875620, your thread was hijacked. A UK or PK constraint needs an index. You can create one (either unique or non-unique) explicitly, or you can let Uncle Oracle create one implicitly when you define the constraint.